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

  • Thread starter Thread starter EagleOne
  • Start date Start date
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
 
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
Click to expand...

=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
 
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
 
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
Click to expand...

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
 
Back
Top