| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tricky Conditional Count
I need to do a conditional count (how many rows are there where the following
conditions are both true). One condition is, does a cell that contains multiple text strings (DEF5678, GHI6789, ABC1234) contain the text string ABC1234? If that condition is true, is the second condition also true? Tell me how many records there are where both conditions are true. I'm very familiar with array formulas {=sum((array1=ABC1234)*(array2=test2))}, and I'm familiar with searching for text strings (=countif(B2:B100,"*"ABC1234"*"). Any thoughts? Steve |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Tricky Conditional Count
Try this
=SUMPRODUCT(--(ISNUMBER(SEARCH("ABC1234",A10:A12))),--(B10:B12="test")) "Steve" wrote: I need to do a conditional count (how many rows are there where the following conditions are both true). One condition is, does a cell that contains multiple text strings (DEF5678, GHI6789, ABC1234) contain the text string ABC1234? If that condition is true, is the second condition also true? Tell me how many records there are where both conditions are true. I'm very familiar with array formulas {=sum((array1=ABC1234)*(array2=test2))}, and I'm familiar with searching for text strings (=countif(B2:B100,"*"ABC1234"*"). Any thoughts? Steve |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Tricky Conditional Count
Barb -
That did it, thanks. Teach me what the double -- does for the formula, please. I appreciate the assistance. Steve "Barb Reinhardt" wrote: Try this =SUMPRODUCT(--(ISNUMBER(SEARCH("ABC1234",A10:A12))),--(B10:B12="test")) "Steve" wrote: I need to do a conditional count (how many rows are there where the following conditions are both true). One condition is, does a cell that contains multiple text strings (DEF5678, GHI6789, ABC1234) contain the text string ABC1234? If that condition is true, is the second condition also true? Tell me how many records there are where both conditions are true. I'm very familiar with array formulas {=sum((array1=ABC1234)*(array2=test2))}, and I'm familiar with searching for text strings (=countif(B2:B100,"*"ABC1234"*"). Any thoughts? Steve |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Tricky Conditional Count
The "double unary minus" changes a boolean (false or true) to a number 0 or
1 so that you can multiply. -- David Biddulph "Steve" wrote in message ... Barb - That did it, thanks. Teach me what the double -- does for the formula, please. "Barb Reinhardt" wrote: Try this =SUMPRODUCT(--(ISNUMBER(SEARCH("ABC1234",A10:A12))),--(B10:B12="test")) "Steve" wrote: I need to do a conditional count (how many rows are there where the following conditions are both true). One condition is, does a cell that contains multiple text strings (DEF5678, GHI6789, ABC1234) contain the text string ABC1234? If that condition is true, is the second condition also true? Tell me how many records there are where both conditions are true. I'm very familiar with array formulas {=sum((array1=ABC1234)*(array2=test2))}, and I'm familiar with searching for text strings (=countif(B2:B100,"*"ABC1234"*"). Any thoughts? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Tricky Conditional Count
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Steve" wrote in message ... Barb - That did it, thanks. Teach me what the double -- does for the formula, please. I appreciate the assistance. Steve "Barb Reinhardt" wrote: Try this =SUMPRODUCT(--(ISNUMBER(SEARCH("ABC1234",A10:A12))),--(B10:B12="test")) "Steve" wrote: I need to do a conditional count (how many rows are there where the following conditions are both true). One condition is, does a cell that contains multiple text strings (DEF5678, GHI6789, ABC1234) contain the text string ABC1234? If that condition is true, is the second condition also true? Tell me how many records there are where both conditions are true. I'm very familiar with array formulas {=sum((array1=ABC1234)*(array2=test2))}, and I'm familiar with searching for text strings (=countif(B2:B100,"*"ABC1234"*"). Any thoughts? Steve |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Tricky Conditional Count
You can use a single "-" no quotes, if you have even criterias, like 2, 4 , 6
and so on... =SUMPRODUCT(-(ISNUMBER(SEARCH("ABC1234",A10:A12))),-(B10:B12="test")) "Steve" wrote: Barb - That did it, thanks. Teach me what the double -- does for the formula, please. I appreciate the assistance. Steve "Barb Reinhardt" wrote: Try this =SUMPRODUCT(--(ISNUMBER(SEARCH("ABC1234",A10:A12))),--(B10:B12="test")) "Steve" wrote: I need to do a conditional count (how many rows are there where the following conditions are both true). One condition is, does a cell that contains multiple text strings (DEF5678, GHI6789, ABC1234) contain the text string ABC1234? If that condition is true, is the second condition also true? Tell me how many records there are where both conditions are true. I'm very familiar with array formulas {=sum((array1=ABC1234)*(array2=test2))}, and I'm familiar with searching for text strings (=countif(B2:B100,"*"ABC1234"*"). Any thoughts? Steve |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Please help me with a Conditional Count... | Excel Discussion (Misc queries) | |||
| Help with tricky IF statement. Somewhat conditional. | Excel Discussion (Misc queries) | |||
| Conditional Count | Excel Worksheet Functions | |||
| conditional count | Excel Worksheet Functions | |||
| tricky conditional formatting | Excel Worksheet Functions | |||
Hybrid Mode
