Excel - counting cells that meet criteria

[amarone]

I have a spreadsheet of data imported from another program. In one column of text cells I need to count all the cells that meet two criteria:

• the first two letters match a specified two-character prefix
• the cell contents are not identical to the cell immediately above.

For example:

ABC
BCD
ABC
ABC
DEF
ABC
BCD
BCD
BCD

I want a formula that for AB will give me the answer 3, for BC the answer 2, and for DE the answer 1.

If necessary, I could add another column that does a LEFT(A1,2) to strip off the characters I am not interested in, but it would be neater to avoid that.

Can anyone help please?

[Spud]

What I’m not clear on is how does the third character affect things… what if you had:

ABC
BCD
ABC
ABX
DEF

Would you count the ABX or or not in the AB total?

Also, if you are just trying to keep it to one cell you can embed the Left function in the formula.

I assume you will have a seperate summary table (Pivot) for the totals?

[amarone]

@Spud

What I’m not clear on is how does the third character affect things… what if you had:

ABC
BCD
ABC
ABX
DEF

Would you count the ABX or or not in the AB total?

Yes. I should have included data like that in my example. I only want to disregard cells whose value exactly matches the cell above.

Also, if you are just trying to keep it to one cell you can embed the Left function in the formula.

That is what I would like to do. I have been trying to use COUNTIFS but have not succeeded in putting the LEFT function into it. (For something else I was doing with the same data, I failed to get LEFT into SUMIFS, but succeeded by using SUM and IF in a CSE function. However, the same technique has not worked for the current issue.)

I assume you will have a seperate summary table (Pivot) for the totals?

Separate table, yes. I don’t know about using pivots.

[JSexton]

The second requirement is the odd one, as it assumes you will never do any resorting of this data. Anyway, I think you need a second column to do this. In column B, starting in row 2, put in =if(a2<>a1,left(a2,2),"") and then copy that all the way down. In row 1, just put in =left(a1,2). That will give you a column with only the non dups, truncated the way you want. If you just want to count a single value at a time, use this: =countif(b1:bwhatever,“AB”) and that will give you your answer.

[amarone]

@JSexton

The second requirement is the odd one, as it assumes you will never do any resorting of this data. Anyway, I think you need a second column to do this. In column B, starting in row 2, put in =if(a2<>a1,left(a2,2),“”) and then copy that all the way down. In row 1, just put in =left(a1,2). That will give you a column with only the non dups, truncated the way you want. If you just want to count a single value at a time, use this: =countif(b1:bwhatever,“AB”) and that will give you your answer.

That seems to do the trick - thank you. No, I will never need to sort the data.