Using a wild card in SUMPRODUCT

[SFC Traver]

I'm using the following formula:

=SUMPRODUCT(--(D61000="*/2-2 IN"),--(E6:E1000'="NBI"))

which returns a value of "0". The "*" could be an A, B, C, D, HHC, or E.

If I replace the * with one of the letters, I get a correct value. How can I
use the wildcard returning a correct value for all the true conditions of
A/2-2, B/2-2, C/2-2, D/2-2, E/2-2, and HHC/2-2?

Thanks!

 

[Shane Devenshire]

Hi,

The SUMPRODUCT function does not support wildcards. Second problem is that
*anything will return anything.

You could use something like this

=SUMPRODUCT(--((D61000="A/2-2 IN")+(D61000="B/2-2 IN")+(D61000="C/2-2
IN")+(D61000="D/2-2 IN")+(D61000="E/2-2 IN")+(D61000="HHC/2-2
IN")>0),--(E6:E1000="NBI"))

 

[T. Valko]

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("/2-2 IN",D61000))),--(E6:E1000="NBI"))

Better to use cells to hold the criteria:

D1 = /2-2 IN
E1 = NBI

=SUMPRODUCT(--(ISNUMBER(SEARCH(D1,D61000))),--(E6:E1000=E1))

 

[SFC Traver]

Thanks, Shane. Works great. I was just hoping there was a less time consuming
way to do it. I even tried using the RIGHT function to no avail. But at least
your solution works!

 

[Rasoul Khoshravan]

Hi,

The SUMPRODUCT function does not support wildcards.  Second problem is that
*anything will return anything.

You could use something like this

=SUMPRODUCT(--((D61000="A/2-2 IN")+(D61000="B/2-2 IN")+(D61000="C/2-2
IN")+(D61000="D/2-2 IN")+(D61000="E/2-2 IN")+(D61000="HHC/2-2
IN")>0),--(E6:E1000="NBI"))

Is there a reason to put >0. Traver's original question doesn't have
this criteria.

 

[T. Valko]

No. It's not needed.

Since a cell can only meet one *or* the other conditions the resulting array
will only return a 1 or 0 so a test for >0 is superfluous.

--
Biff
Microsoft Excel MVP

Hi,

The SUMPRODUCT function does not support wildcards. Second problem is that
*anything will return anything.

You could use something like this

=SUMPRODUCT(--((D61000="A/2-2 IN")+(D61000="B/2-2
IN")+(D61000="C/2-2
IN")+(D61000="D/2-2 IN")+(D61000="E/2-2 IN")+(D61000="HHC/2-2
IN")>0),--(E6:E1000="NBI"))

Is there a reason to put >0. Traver's original question doesn't have
this criteria.

 

[Dave Peterson]

But it gives me a feeling of safety.

If I gave that formula to someone who decided to include an additional "or"
check in a different column, I'd want that ">0" there.

=SUMPRODUCT(--((D61000="A/2-2 IN")+(E6:E1000="something else")+....

 

[Teethless mama]

Try this:

=SUMPRODUCT((RIGHT(D61000,7)="/2-2 IN")*(E6:E1000="NBI"))

 

[T. Valko]

=SUMPRODUCT(--((D61000="A/2-2 IN")+(E6:E1000="something else")+....

Yes, you would need it when testing different arrays for an "or" condition.
The OP is testing the same array for the "or" condition.

For the OP, here's how it works...

Let's assume you want to test A1 to see if it contains either an A or a B.

A1 can contain only one or the other, it can't can't contain both!

Let's assume A1 = A

(A1="A")+(A1="B")

(A1="A") = TRUE
(A1="B") = FALSE

TRUE + FALSE = 1

Now, let's assume A1 = B

(A1="A") = FALSE
(A1="B") = TRUE

FALSE + TRUE = 1

The result of the test can only be 1 or 0. So, in this case, testing for >0

--((A1="A")+(A1="B")>0)

Is redundant since it will return the exact same result.

(A1="A")+(A1="B") = 1
--((A1="A")+(A1="B")>0) = 1

Now, if you're testing different arrays for an "or" condition...

A1 or B1 = A

Let's assume both A1 and B1 = A

(A1="A")+(B1="A")

A1 = "A" = TRUE
B1 = "A" = TRUE

TRUE + TRUE = 2

Since this is an "or" condition you don't want the result to be counted
twice and that's where the test for >0 comes into play.

--((A1="A")+(B1="A")>0) = 1

Hopefully, that will shed some light on it.