SUMPRODUCT() challenge - How to integrate a string value in lieu of a value in a cell .....

E

EagleOne

2003 & 2007


=SUMPRODUCT(($D$1:$D$2=$A2)*($R$1:$R$2=$B$2)) ... the :Rx" and "Bx" values are standard and OK.

D1 = "qwerty IHN zxcv"
D2 = "qwerty zxcv IHN"

A2 cell value = "*IHN*"

If I use =COUNTIF($D$1:$D$2,"*IHN*") the formula works as it correctly yields 2

Is there a way to make SUMPRODUCT(($D$1:$D$2=$A2)*($R$1:$R$2=$B$2)) work
(or get the same end result) if one could:

SUMPRODUCT(("*IHN*")*($R$1:$R$2=$B$2)) I know it does not work.

In short, how can I get a multi-criteria COUNTIF if "IHN" is anywhere in each cell value?

TIA EagleOne
 
R

Ron Rosenfeld

2003 & 2007


=SUMPRODUCT(($D$1:$D$2=$A2)*($R$1:$R$2=$B$2)) ... the :Rx" and "Bx" values are standard and OK.

D1 = "qwerty IHN zxcv"
D2 = "qwerty zxcv IHN"

A2 cell value = "*IHN*"

If I use =COUNTIF($D$1:$D$2,"*IHN*") the formula works as it correctly yields 2

Is there a way to make SUMPRODUCT(($D$1:$D$2=$A2)*($R$1:$R$2=$B$2)) work
(or get the same end result) if one could:

SUMPRODUCT(("*IHN*")*($R$1:$R$2=$B$2)) I know it does not work.

In short, how can I get a multi-criteria COUNTIF if "IHN" is anywhere in each cell value?

TIA EagleOne

=SUMPRODUCT(--ISNUMBER(SEARCH(A2,D1:D2)))

BUT A2="IHN" (no asterisks).


SEARCH(A2,D1:D2) will return an error if IHN is NOT present. So we use
ISNUMBER to return either TRUE (IHN present) or FALSE (IHN not present).

Then the double unary transforms the boolean to 0 or 1; and SUMPRODUCT will add
up the ones -- e.g. give a count.

I'm not sure exactly how you are trying to combine things, but some variant of:

=SUMPRODUCT(ISNUMBER(SEARCH(A2,D1:D3))*ISNUMBER(SEARCH(A3,R1:R3)))

might return the results you are looking for.
--ron
 
E

EagleOne

Perfect!

In bed, I thought about using Search or Find integrated into an "IF" statement to force either a
"0" or "1" all the while concerned whether a no-find threw a 0 or an error (which would blow it up).

Your use of ISNUMERIC is very clever.

In the end, this is how I deployed it:

=SUMPRODUCT((ISNUMBER(SEARCH("IHN",$D$2:$D$1745)))*($R$2:$R$1745=$B$1795))

Thanks for your help now and over time!

EagleOne
 
R

Ron Rosenfeld

Perfect!

In bed, I thought about using Search or Find integrated into an "IF" statement to force either a
"0" or "1" all the while concerned whether a no-find threw a 0 or an error (which would blow it up).

Your use of ISNUMERIC is very clever.

In the end, this is how I deployed it:

=SUMPRODUCT((ISNUMBER(SEARCH("IHN",$D$2:$D$1745)))*($R$2:$R$1745=$B$1795))

Thanks for your help now and over time!

EagleOne

Glad to help. Thanks for the feedback.

And yes, Search/Find returns a #VALUE error if the substring is not found; and
ISNUMBER takes care of that issue.
--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top