SUMPRODUCT. Exact match VS. Search

ghosty

New Member
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!

Last edited:

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
To sum it up even shorter, the question is this. Is there a way to count all 1's in B:B, if and only if there are no 1's in C:D, by using SUMPRODUCT and column titles as criteria?

Oh, I am very sorry for cross-posting, but I cannot edit the first message now. And I really need help

Replies
4
Views
212
Replies
1
Views
141
Replies
7
Views
216
Replies
1
Views
193
Replies
18
Views
629

1,207,423
Messages
6,078,440
Members
446,338
Latest member
AliB

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back