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.

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.

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.

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.

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