How to combine SUMPRODUCT with COUNTIF?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mckzach - 21 Mar 2008 17:34 GMT

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 to this Message

Don Guillett - 21 Mar 2008 17:47 GMT

this will count where the cell in a is Q and the cell in b is claims

=SUMPRODUCT((A1:A100,"q")*(b1:b100="claims"))

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Greetings,
>
[quoted text clipped - 11 lines]
> to
> work. Any help at all would be appreciated. Thanks in advance.

Reply to this Message

mckzach - 21 Mar 2008 18:06 GMT

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).

> this will count where the cell in a is Q and the cell in b is claims
>
[quoted text clipped - 15 lines]
> > to
> > work. Any help at all would be appreciated. Thanks in advance.

Reply to this Message

Dave Peterson - 21 Mar 2008 18:19 GMT

Don had a small typo.

Try this:

=SUMPRODUCT((A1:A100="q")*(b1:b100="claims"))

> Thanks, but that doesn't work either.
> Incidentally, Column A cells may contain from 1 to 15 different letters. My
[quoted text clipped - 25 lines]
> > > to
> > > work. Any help at all would be appreciated. Thanks in advance.

Signature

Dave Peterson

Reply to this Message

Sandy Mann - 21 Mar 2008 18:38 GMT

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:A100)),--(B1:B100="Claims"))

This assumes that *Claims* is the only entry in the matching cells in Column
B

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Thanks, but that doesn't work either.
> Incidentally, Column A cells may contain from 1 to 15 different letters.
[quoted text clipped - 23 lines]
>> > to
>> > work. Any help at all would be appreciated. Thanks in advance.

Reply to this Message

mckzach - 21 Mar 2008 19:01 GMT

Sandy, that worked! Thanks very much for the response.

> If by:
>
[quoted text clipped - 40 lines]
> >> > to
> >> > work. Any help at all would be appreciated. Thanks in advance.

Reply to this Message

Sandy Mann - 21 Mar 2008 19:57 GMT

Glad that it worked for you. Thanks for the feedback.

Signature

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Sandy, that worked! Thanks very much for the response.
>
[quoted text clipped - 45 lines]
>> >> > to
>> >> > work. Any help at all would be appreciated. Thanks in advance.

Reply to this Message

Don Guillett - 21 Mar 2008 19:50 GMT

Array formula that must be entered using ctrl+shift+enter
=SUM(IF(ISERROR(SEARCH("q",$C$1:$C$22)),0,1)*($D$1:$D$22="claims"))

A macro using ucase and lcase
Sub countpartials()
For Each c In Range("c2:c12")
If InStr(UCase(c), "Q") > 0 And LCase(c.Offset(, 1)) _
= "claims" Then ctr = ctr + 1
Next c
MsgBox ctr
End Sub

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

Reply to this Message