SUMPRODUCT with two criteria?

  • Thread starter Thread starter Ed from AZ
  • Start date Start date
E

Ed from AZ

I am SUMPRODUCT to count the number of items in a list that are
"green", except for those which are also "bad". It looks like this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("green",C$3:C$100))))-SUMPRODUCT(--
(ISNUMBER(SEARCH("bad",F$3:F$100))))

Unfortunately, "bad" in F3:F100 can be green, red, or blue!

How can I adjust this so I get:
= (the number of "green" in C3:C100) - (the number of "green" in
C3:C100 with "bad" in F3:F100)

What functions do I need to make this work?

Ed
 
In the 2nd SUMPRODUCT just add another array:

=SUMPRODUCT(--(ISNUMBER(SEARCH("green",C$3:C$100))))-SUMPRODUCT(--(ISNUMBER(SEARCH("green",C$3:C$100))),--(ISNUMBER(SEARCH("bad",F$3:F$100))))
 
=SUMPRODUCT(--(ISNUMBER(SEARCH("green",C$3:C$100))))-SUMPRODUCT(--(ISNUMBER­(SEARCH("green",C$3:C$100))),--(ISNUMBER(SEARCH("bad",F$3:F$100))))

This gives me a #NAME? error ....

How does the second term yield a count of entries with "green" and
"bad" on the same line?

Ed
 
Well, Biff - I dunno what I did, but it works now!

=SUMPRODUCT(--(ISNUMBER(SEARCH("green",C$3:C$100))))-SUMPRODUCT(--
(ISNUMBER(SEARCH("bad",F$3:F$100))),--(ISNUMBER(SEARCH("green",C$3:C
$100))))

The only thing I notice is that I reversed the two terms in the last
half.

I'm still not sure why that second term works, though. Isn't it
multiplying the number of cells that meet each criterion? So if six
cells match "green" and two match "bad", I would get 12 instead of 2?

Ed
 
I see you're posting from Google Groups.

Google Groups is notorious for inserting "formatting characters" in to
formulas.

When I copied and pasted the formula into Excel there was a Google inserted
character after the 2nd ISNUMBER function:

....-SUMPRODUCT(--(ISNUMBER-­(SEARCH("green",C$3:C$100)))...

Remove that character.

Google inserts those as some kind of line break character.

Here's the formula broken down into chunks. Just make sure you enter it all
on one line in Excel:

=SUMPRODUCT(--(ISNUMBER(SEARCH("green",C$3:C$100))))
-SUMPRODUCT(--(ISNUMBER(SEARCH("green",C$3:C$100)))
,--(ISNUMBER(SEARCH("bad",F$3:F$100))))

--
Biff
Microsoft Excel MVP


Ed from AZ said:
=SUMPRODUCT(--(ISNUMBER(SEARCH("green",C$3:C$100))))-SUMPRODUCT(--(ISNUMBER­(SEARCH("green",C$3:C$100))),--(ISNUMBER(SEARCH("bad",F$3:F$100))))
Click to expand...

This gives me a #NAME? error ....

How does the second term yield a count of entries with "green" and
"bad" on the same line?

Ed
 
Here's what the formula is doing:

G = Green
B = Bad

G...B
G.....
G...B
X...B
G.....
G.....
G...X

The 1st SUMPRODUCT counts *all* cells that contain Green:

SUMPRODUCT(--(ISNUMBER(SEARCH("green",C$3:C$100))))

Result = 6

The 2nd SUMPRODUCT counts only those rows that contain *both* Green and Bad:

SUMPRODUCT(--(ISNUMBER(SEARCH("bad",F$3:F$100))),--(ISNUMBER(SEARCH("green",C$3:C
$100))))

Result = 2

These are subtracted to get the final result:

6-2=4
 
I see you're posting from Google Groups.
Google Groups is notorious for inserting "formatting
characters" in to formulas.
Click to expand...

Good to remember!! Thank you!!!
Here's what the formula is doing:
The 2nd SUMPRODUCT counts only those rows that contain *both* Green and Bad:

SUMPRODUCT(--(ISNUMBER(SEARCH("bad",F$3:F$100))),--(ISNUMBER(SEARCH("green"­,C$3:C
$100))))

Result = 2
Click to expand...

I was reading the SUMPRODUCT Help page incorrectly, although I'm still
a bit confused.

It seems when used this way as a text search (vice the probably
intended mathematical uses!), it ceates an array from the specified
ranges and uses the text terms as criteria to return the search
results. Not quite fully explained by "Multiplies corresponding
components in the given arrays, and returns the sum of those
products." <G>

But I know I can use that one in many places!! Thanks for all your
help. I really appreciate this.
Ed
 
I would simplify it to one SP

=SUMPRODUCT(--(ISNUMBER(SEARCH("green",C$3:C$100))),--(NOT(ISNUMBER(SEARCH("bad",F$3:F$100)))))

and if column C contains only green, not say 'this is green' and ditto
column F and bad, you can use

=SUMPRODUCT(--(C$3:C$100="green"),--(F$3:F$100<>"bad"))
 
Back
Top