Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Count Substrings InTwo Columns
I know that using the following SUMPRODUCT function below on the data also
shown below, I get a count result of 2 for matches found in rows 1 and 3. =SUMPRODUCT((A1:A7="a-1")*(B1:B7="s-1")) Col A Col B Row 1 a-1 s-1 Row 2 a-1 s-2 Row 3 a-1 s-1 Row 4 a-4 s-3 Row 5 a-5 s-1 Row 6 b-1 t-1 Row 7 b-2 t-2 However, I can't seem to figure out how to use substrings such that I want to match any "a" in Col A and only where "s-1" appears in Col B. Using the data above, I would expect a count result of 3 for counting rows 1, 3 and 5. Any suggestions would be greatly appreciated. And, thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Count Substrings InTwo Columns
If any "a" means starting with "a"
=SUMPRODUCT(--(LEFT(A1:A7)="a"),--(B1:B7="s-1")) if it means anywhere =SUMPRODUCT(--ISNUMBER(SEARCH("a",A1:A7)),--(B1:B7="s-1")) -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Paputxi" wrote in message ... I know that using the following SUMPRODUCT function below on the data also shown below, I get a count result of 2 for matches found in rows 1 and 3. =SUMPRODUCT((A1:A7="a-1")*(B1:B7="s-1")) Col A Col B Row 1 a-1 s-1 Row 2 a-1 s-2 Row 3 a-1 s-1 Row 4 a-4 s-3 Row 5 a-5 s-1 Row 6 b-1 t-1 Row 7 b-2 t-2 However, I can't seem to figure out how to use substrings such that I want to match any "a" in Col A and only where "s-1" appears in Col B. Using the data above, I would expect a count result of 3 for counting rows 1, 3 and 5. Any suggestions would be greatly appreciated. And, thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Total Summed Count of Numeric Repeat (Paired /Double Instance) Criteria | Excel Worksheet Functions | |||
how to count one value or another across multiple columns? | Excel Discussion (Misc queries) | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
count number of cells based on TWO conditions (2 different columns | Excel Worksheet Functions |