SUMPRODUCT Difficulty

**darkhat01** · 09-24-2008, 02:35 PM

I am tring to figure out what is wrong with my formula.

Works if I am looking just for the word "Mainframe"
=SUMPRODUCT((D2:D499="Mainframe")*(I2:I499=O51))

Returns the count of 16

This is the part that does not work...

Does not work if I am looking for anything that contains the word "Mainframe"
=SUMPRODUCT((D2:D499="Mainframe*")*(I2:I499=O51))

Myunderstanding is all I need to do is add the wild card to make this work.

Should return the count of 18

Example of data

D2 - D499 I2 - I499 O51 = Yellow Proven
Mainframe Yellow Proven
Mainframe Yellow Proven
Mainframe Yellow Proven
Mainframe Yellow Proven
Mainframe Yellow Proven
Mainframe Yellow Proven
Mainframe Yellow Proven
MainframeTest Yellow Proven
Mainframe Yellow Proven
Mainframe Yellow Proven
Mainframe Yellow Proven
Mainframe Yellow Proven
Mainframe Yellow Proven
Mainframe Yellow Proven
Mainframe Test Red Proven
Windows Yellow Proven
Mainframe Yellow Proven
Mainframe Yellow Proven
Mainframe Yellow Proven
Mainframe Red Proven

Thanks so much for any help,

Darkhat

**NBVC** · 09-24-2008, 02:39 PM

Wildcards don't work with "=" comparators... only works with , comparators like Countif, Sumif and Match...

Anyways try:

=SUMPRODUCT(Isnumber(Search("Mainframe",D2:D499))*(I2:I499=O51))

**darkhat01** · 09-25-2008, 02:54 PM

Originally Posted by NBVC

=SUMPRODUCT(Isnumber(Search("Mainframe",D2:D499))*(I2:I499=O51))

This looks like it works perfect.... Thanks soooooooooooo much....

**darkhat01** · 09-25-2008, 03:02 PM

Before if I wanted to count any numbers that are not Mainframe I entered this...

=SUMPRODUCT((D2:D499<>"Mainframe")*(I2:I499=O51))

How do i do this with the new formula?
=SUMPRODUCT(Isnumber(Search("Mainframe",D2:D499))*(I2:I499=O51))

Thanks,

Darkhat