COUNTIF

[D VanD]

I can't find anything in the help. I need to be to count more than one
criteria. Is this possible?

As an example.

A B
1 New York Baseball
2 New York Football
3 Cincinnati Baseball
4 Cleveland Baseball

I want to be able to display this in another workbook

B C
Baseball Football
1 New York 1 1
2 Cincinnati 1 0
3 Cleveland 1 0

So as an example, what formula can I use in cell B1 to count both criteria,
both New York and baseball.

Thanks in advance
Doug

 

[Don Guillett]

=sumproduct((a2:a200="New York")*(b2:b200={"Baseball","Football"}))

 

[Don Guillett]

Oops, I read too quickly. TWO formulas
=sumproduct((sheet1!$a$2:$a$200="New York")*(sheet1!$b$2:$b$200="Football")
for one & just change the football to baseball for the other.

 

[D VanD]

Awesome. Thanks Don.

Oops, I read too quickly. TWO formulas
=sumproduct((sheet1!$a$2:$a$200="New York")*(sheet1!$b$2:$b$200="Football")
for one & just change the football to baseball for the other.

 

[Bob Phillips]

To ensure drag-ability for the formula, use

=SUMPRODUCT((Sheet1!$A$2:$A$100=$A2)*(Sheet1!$A$2:$A$100=B$1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Don Guillett]

glad to help

 

[D VanD]

One last question!!!

I have this formula

=SUMPRODUCT((KOP!$C$2:$C$40000=$A10)*(KOP!$B$2:$B$40000="81-AOL
")*(KOP!$A$2:$A$40000>=DATEVALUE("02/01/2004")))

This works fine

I want to be able to populate the DATEVALUE("02/01/2004") from a cell value
rather than having to change the formula each month.

I tried this but it didn't work

Cell A3 is set to 02/01/2004

=SUMPRODUCT((KOP!$C$2:$C$40000=$A9)*(KOP!$B$2:$B$40000="81-AOL
")*(KOP!$A$2:$A$40000>=DATEVALUE(A3)))

Thanks

 

[Don Guillett]

if a3 is a date just use =a3

 

[Olly]

Why not use a Pivot Table?
[/QUOTE]