Help with COUNTIF
Posted by Reed on February 03, 2002 8:21 PM
OK. I have a column of cells that have either nothing in them or one of the following:
CR
CC
CI
I
Now these can be mixed to like this:
CR, CR, CC
The thing is there will never be more than 3 C types in a cell and never more than 1 I. So themost full cell that is possible is like this:
CR, CR, CR, I
Now I want a COUNTIF statement that will count the CRs, CCs and CIs. To do this I tried the following:
=SUM(COUNTIF(SunList, LEN=2))+((COUNTIF(SunList, LEN=6)*2)+
And so on...
The problem is this doesn't work. Anyone have an idea on how I can do a formula that will do this?
Posted by Aladin Akyurek on February 03, 2002 9:22 PM
=SUMPRODUCT((LEN($A$2:$A$40)-LEN(SUBSTITUTE(LOWER($A$2:$A$40),LOWER(E1),"")))/LEN(E1))
where $A$2:$A$40 is the range of interest and E1 holds the substring whose occurences you want to count (like CR or CC).
===========
Posted by Reed on February 03, 2002 9:35 PM
WOW! Thank you very much. That formula works even better then what I was trying to do.
Once again Thanks!
Reed Dotto
