Count text based on multiple (2) conditions

[Leathal711]

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.

 

[Biff]

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]

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]

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

 

[Dave Peterson]

=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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html