Sumproduct Help

[EricG]

Let's say I have in column A a series of strings, and in Column B either
"Yes" or "No". I would like to count the number of occurrences where I have
a certain sub-string (say "XYZ") in the cell in column A, and where the cell
in column B in the same row is "No". Do this for rows 2 to 100.

I can use =COUNTIF(A2:A100,"*XYZ*") to get the first count, but I can't
figure out how to combine it with the second criterion of "No" in column B.
Is this a good time to use SUMPRODUCT, which I have yet to learn?

Thanks,

Eric

 

[Pete_UK]

COUNTIF (and SUMIF) are fine if you only have one condition, but with
2 or more then you will need to use SUMPRODUCT (or an array-entered
formula). You can't use wildcards with SP, and it is a good idea just
to use the relevant range(s) - in versions before Excel 2007 you can't
use full column references, and even though you can with XL2007 it is
better not to. So, the formula you need is something like this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("xyz",A2:A100))),--(B2:B100="No"))

You can use FIND instead of SEARCH if the case of your substring is
important. The formula will count the number of instances in rows 2 to
100 where column B is "No" and column A contains the (sub)string
"xyz".

You can also use SUMPRODUCT to sum a column dependent on some
(multiple) criteria. For example, if you wanted to get a total from
numbers in column C which matched the same criteria, you would have:

=SUMPRODUCT(--(ISNUMBER(SEARCH("xyz",A2:A100))),--
(B2:B100="No"),C2:C100)

Note there is no condition for the column C term.

Hope this helps.

Pete

 

[EricG]

Stefi,

That did the trick! Köszönöm!. Now I have one example of SUMPRODUCT to
learn from.

Regards,

Eric

 

[Stefi]

You are welcome! Thanks for the feedback!
Örülök, hogy segíthettem! Kösz a visszajelzést!
Stefi
--
Regards!
Stefi



„EricG†ezt írta: