Using a wild card in SUMPRODUCT
 

I'm using the following formula:

=SUMPRODUCT(--(D6:D1000="*/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!

Using a wild card in SUMPRODUCT
 

Hi,

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

You could use something like this

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

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"SFC Traver" wrote:

I'm using the following formula:

=SUMPRODUCT(--(D6:D1000="*/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!

Using a wild card in SUMPRODUCT
 

Try this:

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

Better to use cells to hold the criteria:

D1 = /2-2 IN
E1 = NBI

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

--
Biff
Microsoft Excel MVP


"SFC Traver" wrote in message
...
I'm using the following formula:

=SUMPRODUCT(--(D6:D1000="*/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!

Using a wild card in SUMPRODUCT
 

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!

"Shane Devenshire" wrote:

Hi,

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

You could use something like this

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

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"SFC Traver" wrote:

I'm using the following formula:

=SUMPRODUCT(--(D6:D1000="*/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!

Using a wild card in SUMPRODUCT
 

On Feb 16, 8:04*am, Shane Devenshire
wrote:
Hi,

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

You could use something like this

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


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

Using a wild card in SUMPRODUCT
 

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


"Rasoul Khoshravan" wrote in message
...
On Feb 16, 8:04 am, Shane Devenshire
wrote:
Hi,

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

You could use something like this

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


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

Using a wild card in SUMPRODUCT
 

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(--((D6:D1000="A/2-2 IN")+(E6:E1000="something else")+....



Rasoul Khoshravan wrote:

On Feb 16, 8:04 am, Shane Devenshire
wrote:
Hi,

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

You could use something like this

=SUMPRODUCT(--((D6:D1000="A/2-2 IN")+(D6:D1000="B/2-2 IN")+(D6:D1000="C/2-2
IN")+(D6:D1000="D/2-2 IN")+(D6:D1000="E/2-2 IN")+(D6:D1000="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

Using a wild card in SUMPRODUCT
 

Try this:

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


"SFC Traver" wrote:

I'm using the following formula:

=SUMPRODUCT(--(D6:D1000="*/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!

Using a wild card in SUMPRODUCT
 

=SUMPRODUCT(--((D6:D1000="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.


--
Biff
Microsoft Excel MVP


"Dave Peterson" wrote in message
...
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(--((D6:D1000="A/2-2 IN")+(E6:E1000="something else")+....



Rasoul Khoshravan wrote:

On Feb 16, 8:04 am, Shane Devenshire
wrote:
Hi,

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

You could use something like this

=SUMPRODUCT(--((D6:D1000="A/2-2 IN")+(D6:D1000="B/2-2
IN")+(D6:D1000="C/2-2
IN")+(D6:D1000="D/2-2 IN")+(D6:D1000="E/2-2 IN")+(D6:D1000="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