Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Avoiding counting the same cell twice
Please help, I'm stuck.
How can I count the number of cells containing certain words but avoid counting the same cell twice. E.g. Column A A B C A B A A B B A How do I count the number of cells containing A added to the number of cells that contain B without double counting those that contain A and B The answer in the above example should be 6 (NOT 7) Many thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Avoiding counting the same cell twice
=SUMPRODUCT(--(LEFT(A1:A7,1)={"A","B"}))
--------------- mama no teeth "JRD" wrote: Please help, I'm stuck. How can I count the number of cells containing certain words but avoid counting the same cell twice. E.g. Column A A B C A B A A B B A How do I count the number of cells containing A added to the number of cells that contain B without double counting those that contain A and B The answer in the above example should be 6 (NOT 7) Many thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Avoiding counting the same cell twice
One way...
If you download and install the latest version of the morefunc.xll add-in, you can invoke: =SUM((REGEX.SUBSTITUTE(UPPER(A2:A8),"[ AB]","")="")+0)-COUNTBLANK(A2:A8) which must be confirmed with control+shift+enter, not just with enter. JRD wrote: Please help, I'm stuck. How can I count the number of cells containing certain words but avoid counting the same cell twice. E.g. Column A A B C A B A A B B A How do I count the number of cells containing A added to the number of cells that contain B without double counting those that contain A and B The answer in the above example should be 6 (NOT 7) Many thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
insert date | Excel Worksheet Functions | |||
Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
Instead of a negative number, I'd like to show zero... | Excel Worksheet Functions | |||
cell color index comparison | New Users to Excel | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions |