SUMPRODUCT/ISNUMBER

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hell

How do I combine the following into one argument, looking for cells containing "Passed" and "Y" and "Homestyle" or "Encompass" or "Netspan

=SUMPRODUCT(ISNUMBER(FIND("Passed",J2:J1696))*ISNUMBER(FIND("Y",K2:K1696))*ISNUMBER(FIND("Homestyle",C2:C1696)))
=SUMPRODUCT(ISNUMBER(FIND("Passed",J2:J1696))*ISNUMBER(FIND("Y",K2:K1696))*ISNUMBER(FIND("Encompass",C2:C1696)))
=SUMPRODUCT(ISNUMBER(FIND("Passed",J2:J1696))*ISNUMBER(FIND("Y",K2:K1696))*ISNUMBER(FIND("Netspan",C2:C1696)))

Regards

Rachael
 
Maybe something like this

==SUMPRODUCT(ISNUMBER(FIND("Passed",J2:J1696))*ISNUMBER(FIND("Y",K2:K1696))*
(ISNUMBER(FIND("Homestyle",C2:C1696))+ISNUMBER(FIND("Encompass",C2:C1696))+I
SNUMBER(FIND("Netspan",C2:C1696))))

(not tested but the grand design should work, OR is represented by + in an
array formula)

Having said that some things to remember, FIND is case sensitive so it won't
pick up "y" or "netspan"
and so on. The only reason to use isnumber(find/search is because there are
other characters in the same cells,
if you only want to count cells that only contains these things you can
remove that part

--

Regards,

Peo Sjoblom


Rachael said:
Hello

How do I combine the following into one argument, looking for cells
Click to expand...
containing "Passed" and "Y" and "Homestyle" or "Encompass" or "Netspan"
 
Hi
try

=SUMPRODUCT(ISNUMBER(FIND("Passed",J2:J1696))*ISNUMBER(FIND
("Y",K2:K1696))*(ISNUMBER(FIND("Homestyle",C2:C1696))
+ISNUMBER(FIND("Encompass",C2:C1696))+ISNUMBER(FIND
("Netspan",C2:C1696))>0))


-----Original Message-----
Hello

How do I combine the following into one argument, looking
Click to expand...
for cells containing "Passed" and "Y" and "Homestyle"
or "Encompass" or "Netspan"
 
Hello Rachael,

=SUMPRODUCT(ISNUMBER(FIND("Passed",J2:J1696))*ISNUMBER(FIND("Y",K2:K1696))*
(ISNUMBER(FIND({"Homestyle","Encompass","Netspan"},C2:C1696))))

Salutations,

Daniel M.

Rachael said:
Hello

How do I combine the following into one argument, looking for cells containing
Click to expand...
"Passed" and "Y" and "Homestyle" or "Encompass" or "Netspan"
 
=SUMPRODUCT(ISNUMBER(FIND("Passed",J2:J1696))*ISNUMBER(FIND("Y",K2:K1696))*
(ISNUMBER(FIND({"Homestyle","Encompass","Netspan"},C2:C1696))))
Click to expand...
...

I wonder what the correct answer would be for cells in col C containing two or
all three of the alternatives? If, for example, J11 contained Passed, K11
contained Y, and C11 contained both Homestyle and Netspan, that row would
contribute 2 to the overall count. Likely a nonissue for the OP, but if this
example should only contribute 1 to the overall count, the formula needs to be
adapted to

=SUMPRODUCT(--(MMULT(--ISNUMBER(FIND("Passed",J2:J1696)*FIND("Y",K2:K1696)
*FIND({"Homestyle","Encompass","Netspan"},C2:C1696)),{1;1;1})>0))

Also note that only one ISNUMBER call is needed.
 
Hello,

Harlan Grove said:
Also note that only one ISNUMBER call is needed.
Click to expand...

I will use that hint to simplify my formula. As you noted, it works if each cell
in column C contains at most ONE of the 3 keywords.

=SUMPRODUCT(--ISNUMBER(FIND("Passed",J2:J1696)*FIND("Y",K2:K1696)
*FIND({"Homestyle","Encompass","Netspan"},C2:C1696)))

Regards,

Daniel M.
 
Hell

I would also like to change the formula to count "A", "C" or "N" in Column K rather than just "Y"

=SUMPRODUCT(--ISNUMBER(FIND("Passed",J2:J1696)*FIND("Y",K2:K1696
*FIND({"Homestyle","Encompass","Netspan"},C2:C1696))

Copying & amending the argument for Column C doesn't seem to work

Any ideas

Rachael
 
I would also like to change the formula to count "A", "C" or "N" in Column K rather than just "Y":

=SUMPRODUCT(--ISNUMBER(FIND("Passed",J2:J1696)*FIND("Y",K2:K1696)
*FIND({"Homestyle","Encompass","Netspan"},C2:C1696)))
Click to expand...
...

Unclear whether 'Y' would still be sought in addition to 'A', 'C' and 'N' in
column K. I'll assume it should be.

=SUMPRODUCT((SUBSTITUTE(J2:J1696,"Passed","")<>J2:J1696)
*(MMULT(--(SUBSTITUTE(K2:K1696,{"A","C","N","Y"},"")<>K2:K1696),{1;1;1;1})>0),
*(SUBSTITUTE(C2:C1696,{"Homestyle","Encompass","Netspan"},"")<>C2:C1696))

This treats multiple instances of 'A', 'C', 'N' or 'Y' in any cell in col K as a
single match. If you want, e.g., "No wAY" if it appeared in any cell in the col
K range to count as 3 matches, change the formula to

=SUMPRODUCT((SUBSTITUTE(J2:J1696,"Passed","")<>J2:J1696)
*MMULT(--(SUBSTITUTE(K2:K1696,{"A","C","N","Y"},"")<>K2:K1696),{1;1;1;1}),
*(SUBSTITUTE(C2:C1696,{"Homestyle","Encompass","Netspan"},"")<>C2:C1696))
 
Back
Top