How to combine SUMPRODUCT with COUNTIF?
 

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.

How to combine SUMPRODUCT with COUNTIF?
 

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.

How to combine SUMPRODUCT with COUNTIF?
 

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.

How to combine SUMPRODUCT with COUNTIF?
 

Don had a small typo.

Try this:

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

mckzach wrote:

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.



--

Dave Peterson

How to combine SUMPRODUCT with COUNTIF?
 

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.

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.

How to combine SUMPRODUCT with COUNTIF?
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

How to combine SUMPRODUCT with COUNTIF?
 

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

--

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