| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count text based on multiple (2) conditions
I want to be able to count the number of times text occurs in one column
based on a match with a different condition in the second column. The formula: =SUM(IF{(A199:A216="Karratha",IF(B199:B206="DET",1 ,0)))} returns #N/A when entered as an array and returns 1 when entered as normal. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count text based on multiple (2) conditions
Hi!
Try one of these: =SUM(COUNTIF(A199:A216,{"Karratha","DET"})) =COUNTIF(A199:A216,"Karratha")+COUNTIF(A199:A216," DET") =SUMPRODUCT(--(ISNUMBER(MATCH(A199:A216,{"Karratha","DET"},0))) Biff "Leathal711" wrote in message ... I want to be able to count the number of times text occurs in one column based on a match with a different condition in the second column. The formula: =SUM(IF{(A199:A216="Karratha",IF(B199:B206="DET",1 ,0)))} returns #N/A when entered as an array and returns 1 when entered as normal. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count text based on multiple (2) conditions
Hi Biff
I think we are getting there (!) however, the second condition (DET) is in the second column (B). Data is as follows: Karratha DET Karratha DET Karratha DET Karratha DET Karratha DET Karratha DET Karratha DET Karratha DET Karratha DHW Karratha DOH Karratha DOJ Karratha DPI Karratha DSR Karratha FESA Karratha FESA Karratha Police Karratha DEC Karratha DCD So, to get the answer of how many DETs are in Karratha, it should equal 8. I am trying to find a formula I can apply to a larger section and just tryiing to get it to work with a simple one. Cheers, Leanne. "Biff" wrote: Hi! Try one of these: =SUM(COUNTIF(A199:A216,{"Karratha","DET"})) =COUNTIF(A199:A216,"Karratha")+COUNTIF(A199:A216," DET") =SUMPRODUCT(--(ISNUMBER(MATCH(A199:A216,{"Karratha","DET"},0))) Biff "Leathal711" wrote in message ... I want to be able to count the number of times text occurs in one column based on a match with a different condition in the second column. The formula: =SUM(IF{(A199:A216="Karratha",IF(B199:B206="DET",1 ,0)))} returns #N/A when entered as an array and returns 1 when entered as normal. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count text based on multiple (2) conditions
Ooops!
Sorry, I wasn't paying attention. Try this: =SUMPRODUCT(--(A199:A216="Karratha"),--(B199:B216="DET")) I also just noticed in your formula: =SUM(IF{(A199:A216="Karratha",IF(B199:B206="DET", 1,0))) You have unequal range sizes: A199:A216 and B199:B206 They have to be exactly the same size. Biff "Leathal711" wrote in message ... Hi Biff I think we are getting there (!) however, the second condition (DET) is in the second column (B). Data is as follows: Karratha DET Karratha DET Karratha DET Karratha DET Karratha DET Karratha DET Karratha DET Karratha DET Karratha DHW Karratha DOH Karratha DOJ Karratha DPI Karratha DSR Karratha FESA Karratha FESA Karratha Police Karratha DEC Karratha DCD So, to get the answer of how many DETs are in Karratha, it should equal 8. I am trying to find a formula I can apply to a larger section and just tryiing to get it to work with a simple one. Cheers, Leanne. "Biff" wrote: Hi! Try one of these: =SUM(COUNTIF(A199:A216,{"Karratha","DET"})) =COUNTIF(A199:A216,"Karratha")+COUNTIF(A199:A216," DET") =SUMPRODUCT(--(ISNUMBER(MATCH(A199:A216,{"Karratha","DET"},0))) Biff "Leathal711" wrote in message ... I want to be able to count the number of times text occurs in one column based on a match with a different condition in the second column. The formula: =SUM(IF{(A199:A216="Karratha",IF(B199:B206="DET",1 ,0)))} returns #N/A when entered as an array and returns 1 when entered as normal. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count text based on multiple (2) conditions
=sumproduct(--(a1:a100="Karratha"),--(b1:b100="Det"))
Adjust the ranges to match--but you can't use whole columns. =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Leathal711 wrote: Hi Biff I think we are getting there (!) however, the second condition (DET) is in the second column (B). Data is as follows: Karratha DET Karratha DET Karratha DET Karratha DET Karratha DET Karratha DET Karratha DET Karratha DET Karratha DHW Karratha DOH Karratha DOJ Karratha DPI Karratha DSR Karratha FESA Karratha FESA Karratha Police Karratha DEC Karratha DCD So, to get the answer of how many DETs are in Karratha, it should equal 8. I am trying to find a formula I can apply to a larger section and just tryiing to get it to work with a simple one. Cheers, Leanne. "Biff" wrote: Hi! Try one of these: =SUM(COUNTIF(A199:A216,{"Karratha","DET"})) =COUNTIF(A199:A216,"Karratha")+COUNTIF(A199:A216," DET") =SUMPRODUCT(--(ISNUMBER(MATCH(A199:A216,{"Karratha","DET"},0))) Biff "Leathal711" wrote in message ... I want to be able to count the number of times text occurs in one column based on a match with a different condition in the second column. The formula: =SUM(IF{(A199:A216="Karratha",IF(B199:B206="DET",1 ,0)))} returns #N/A when entered as an array and returns 1 when entered as normal. -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| parsing text in a multiple column count | Excel Worksheet Functions | |||
| Justify text across multiple columns | Excel Discussion (Misc queries) | |||
| Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
| SUM based on multiple conditions - SORRY, URGENT!!! | Excel Worksheet Functions | |||
| Can I count in an array based on a function? | Excel Worksheet Functions | |||
Linear Mode
