Hi! I've got some tricky problem.
Let's say, I have this kind of table:
What I need is to sum all 1's if they have something to do with sociology and have 'high' in A:A.
The problem is twofold: 1) I do not need to count the value in B2, since 'sociology' is already mentioned in C2. But I need to count B3, since sociology is mentioned only here.
2) I need to do this using SUMPRODUCT.
So in the end, I would like to get 2, and not 3. I thought I could at first sum all the 1's, when the column title does not match "sociology" exactly, but contains "sociology" as a part of it. Then I would sum all 1's, when the title matches "sociology" exactly and there are no cases when "1" exists in columns which have "sociology" in their titles. And then I would sum the first and the second.
The first part seems to be easy:
=SUMPRODUCT(($B$2:$D$4=1)*($B$1:$D$1<>"sociology")*(ISNUMBER(SEARCH("sociology",$B$1:$D$1)))*($A$2:$A$4="high"))
This would result in "1", which is expected. This would count only C2.
The second part I thought should have been something like this:
=SUMPRODUCT(($B$2:$D$4=1)*($B$1:$D$1="sociology")*(NOT(ISNUMBER(SEARCH("sociology ",$B$1:$D$1))))*($A$2:$A$4="high"))
This does not work as intended, since here I get "2", since it counts both B2 and B3:
=SUMPRODUCT(($B$2:$D$4=1)*({TRUE,FALSE,FALSE})*({TRUE,FALSE,FALSE})*($A$2:$A$4="high"))
The question is how should I make the second part work just like the first and to count only B3.
Thank you very much in advance!
Let's say, I have this kind of table:
What I need is to sum all 1's if they have something to do with sociology and have 'high' in A:A.
The problem is twofold: 1) I do not need to count the value in B2, since 'sociology' is already mentioned in C2. But I need to count B3, since sociology is mentioned only here.
2) I need to do this using SUMPRODUCT.
So in the end, I would like to get 2, and not 3. I thought I could at first sum all the 1's, when the column title does not match "sociology" exactly, but contains "sociology" as a part of it. Then I would sum all 1's, when the title matches "sociology" exactly and there are no cases when "1" exists in columns which have "sociology" in their titles. And then I would sum the first and the second.
The first part seems to be easy:
=SUMPRODUCT(($B$2:$D$4=1)*($B$1:$D$1<>"sociology")*(ISNUMBER(SEARCH("sociology",$B$1:$D$1)))*($A$2:$A$4="high"))
This would result in "1", which is expected. This would count only C2.
The second part I thought should have been something like this:
=SUMPRODUCT(($B$2:$D$4=1)*($B$1:$D$1="sociology")*(NOT(ISNUMBER(SEARCH("sociology ",$B$1:$D$1))))*($A$2:$A$4="high"))
This does not work as intended, since here I get "2", since it counts both B2 and B3:
=SUMPRODUCT(($B$2:$D$4=1)*({TRUE,FALSE,FALSE})*({TRUE,FALSE,FALSE})*($A$2:$A$4="high"))
The question is how should I make the second part work just like the first and to count only B3.
Thank you very much in advance!
Last edited:
