Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine SUMIF and SUMPRODUCT | Excel Worksheet Functions | |||
Combine COUNTIF & EXACT | Excel Discussion (Misc queries) | |||
Do I need to combine hlookup and countif????? | Excel Worksheet Functions | |||
How do I combine 2 COUNTIF formulas?? | Excel Worksheet Functions | |||
SUMPRODUCT - How to combine | Excel Worksheet Functions |