Thread
:
How to combine SUMPRODUCT with COUNTIF?
View Single Post
#
6
Posted to microsoft.public.excel.worksheet.functions
mckzach
external usenet poster
Posts: 10
How to combine SUMPRODUCT with COUNTIF?
Sandy, that worked! Thanks very much for the response.
"Sandy Mann" wrote:
If by:
dilemma is that I'm only looking for those including "Q"
Your mean that there could be other letters in the cell then use your own
formula:
=SUMPRODUCT((LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,"Q",""))),--(B1:B100="Claims"))
Note however that it is case ensitive and it will return wrong results if
there are more than one *Q* in the cell. To avaid there errors try:
=SUMPRODUCT(--(LEN(SUBSTITUTE(UPPER(A1:A100),"Q",""))<LEN(A1:A1 00)),--(B1:B100="Claims"))
This assumes that *Claims* is the only entry in the matching cells in Column
B
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
Replace @mailinator.com with @tiscali.co.uk
"mckzach" wrote in message
...
Thanks, but that doesn't work either.
Incidentally, Column A cells may contain from 1 to 15 different letters.
My
dilemma is that I'm only looking for those including "Q" (and "Claims" in
Column B).
"Don Guillett" wrote:
this will count where the cell in a is Q and the cell in b is claims
=SUMPRODUCT((A1:A100,"q")*(b1:b100="claims"))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"mckzach" wrote in message
...
Greetings,
I need to count up the number of occurrences of the letter "Q" in
Column
A,
only if the string in Column B = "Claims."
That is, how do I combine . . .
=SUMPRODUCT(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,"Q","")))
AND
=COUNTIF(B1:B100,"Claims")
I have tried various approaches and different formulas, but nothing
seems
to
work. Any help at all would be appreciated. Thanks in advance.
Reply With Quote
mckzach
View Public Profile
Find all posts by mckzach