Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Multiple Instances of Text in a Cell
Ooops!
I see you're testing a range, not a single cell: =SUMPRODUCT(LEN(I2:I44)-LEN(SUBSTITUTE(I2:I44,"blue","")))/LEN("blue") =SUMPRODUCT(LEN(I2:I44)-LEN(SUBSTITUTE(UPPER(I2:I44),"BLUE","")))/LEN("blue") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: =(LEN(A12)-LEN(SUBSTITUTE(A12,"blue","")))/LEN("blue") Note that SUBSTITUTE is case sensitive. This version takes case into account: =(LEN(A12)-LEN(SUBSTITUTE(UPPER(A12),"BLUE","")))/LEN("blue") -- Biff Microsoft Excel MVP "dave roth" wrote in message ... Good Afternoon: I need to be able to count multiple instances of the same piece of text in a given cell, i.e. "blue, blue, red, green." How I currently have this setup is: =countif(I2:I44,"*blue*") =countif(I2:I44,"*green*") =countif(I2:I44,"*red*") on separate rows. In the above example these will return 1 blue, 1 green, and 1 red; I need it to return 2 blues. Thanks very much, and a good weekend to all. Dave Roth |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Multiple Instances of Text in a Cell | Excel Discussion (Misc queries) | |||
Count Multiple Instances of Text in a Cell | Excel Discussion (Misc queries) | |||
count # of instances in cell (a b a) answer 2 formula if possible | Excel Worksheet Functions | |||
cell formula for counting instances of text? | Excel Discussion (Misc queries) | |||
A counting formula that won't count multiple instances of same val | Excel Worksheet Functions |