| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 35317 | 2003-07-08 12:36:00 | Excel - Conditional Search/Sum or Count | GrahamB (750) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 158295 | 2003-07-08 12:36:00 | I have a column of identifiers (eg a1, a2, a3, a1b1, a1b2, a1b1c1, a1b2c2, a2b1c3 etc). I need to count the numbers that are an extention of an earlier identifier. (eg a1b1c1 is an extention of a1b1; a1b2c2 is an extention of a1b2; and a1b1, a1b2, a1b1c1 and a1b2c2 are all extentions of a1). I can end each identifier with a cedilla then FIND the length of the identifier OK, but what I need is a formula that either sums or counts the range a1:a100 IF the elements within the range are an extention of the identifier under examination. It doesn't matter if the formula also counts/sums the orginating identifier. I need to make this calculation for each individual elemnt within the A1:a100 range. I will appreciate any bright ideas, but they cannot involve creating a separate column along-side the Column of identifiers, unless it is a calc that is then used by all the individual Formulae . The formula has to fit into a number of cells on the same row as the identifier under consideration. I have a small excel spreadsheet as an example that may make this more evident. If anyone wants it, contact me directly on graham@ariel.co.nz TFYH Graham Bockett |
GrahamB (750) | ||
| 158296 | 2003-07-08 21:26:00 | The countif function is the winner, but not without doing some intermediate steps. You need a column that strips out the earlier identifiers, otherwise it just won't work, ie left (a1,2) to get the shorter ones. That could be way out of the way on the right or hidden if need be. If you wanted to use and IF statement to help make it smarter, you could, but I wouldn't use a cedilla as a separator. say: =IF(MID(a1,3,1)="~",LEFT(a1,2),IF(MID(a1,5,1)="~",LEFT(A1,4),"")) You could nest the ifs quite a bit if you wanted. Then you need to use countif(b1:b100,"a1") to count the a1s, and so on. A separate count formula in a separate cell for each unique identifier you wish to count. robo. |
robo (205) | ||
| 158297 | 2003-07-08 21:50:00 | If you want to count all the cells starting with "a1" use =COUNTIF(A1:A100,"a1*") If you want to count all the cells containing "b1" use =COUNTIF(A1:A100,"*b1*") If you want to count all the cells starting with "a1" but with only two following characters use =COUNTIF(A1:A100,"a1??") The "*" and "?" are wildcard charactes. HTH |
Russell D (18) | ||
| 158298 | 2003-07-09 05:35:00 | Thanks Russell, The wild card should do the trick. I'll come back if it doesn't work out. Many thanks Graham Bockett |
GrahamB (750) | ||
| 1 | |||||