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

[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

 

[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,D12)))

BUT A2="IHN" (no asterisks).


SEARCH(A2,D12) 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,D13))*ISNUMBER(SEARCH(A3,R1:R3)))

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

 

[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

 

[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