| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting number of cells that contain certain text but not cell tw
How can I count cells that contain certain words without counting same cell
twice. e.g. Column A 1 blue, green, white 2 white, yellow, red 3 red, blue, cyan 4 green, cyan, black 5 indigo, green, blue 6 yellow, red. black How can I count the number of cells that contain blue, white or green The answer in this case would be 4. Many thanks John |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of cells that contain certain text but not cell tw
Shouldn't the correct answer be 5? If so, try...
=SUM(--(MMULT(--ISNUMBER(SEARCH({"blue","white","green"},A1:A6)),{ 1;1;1}) 0)) or =SUM(--(MMULT(--ISNUMBER(SEARCH(C1:E1,A1:A6)),TRANSPOSE(COLUMN(C1: E1)^0)) 0)) ....where C1:E1 contains blue, green, and white. Note that the second formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , JRD wrote: How can I count cells that contain certain words without counting same cell twice. e.g. Column A 1 blue, green, white 2 white, yellow, red 3 red, blue, cyan 4 green, cyan, black 5 indigo, green, blue 6 yellow, red. black How can I count the number of cells that contain blue, white or green The answer in this case would be 4. Many thanks John |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Text entries behaving like numbers | Excel Discussion (Misc queries) | |||
| Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
| checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
| how to count the number of text frequencies and copy to other cell | Excel Worksheet Functions | |||
| Help adding text values | Excel Worksheet Functions | |||
Hybrid Mode
