SUMPRODUCT with two criteria?

[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

 

[T. Valko]

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))))

 

[Ed from AZ]

=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

 

[Ed from AZ]

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

 

[T. Valko]

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

=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

 

[T. Valko]

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

 

[Ed from AZ]

I see you're posting from Google Groups.

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

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

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

 

[Bob Phillips]

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"))