Count with multiple conditions

[Toby0924]

Hello all!

I have been reading the discussion groups and any website I can find on
countif functions with multiple criteria and I cannot seem to find the
answer. I have a spreadsheet that looks something like this:

A B
Dale 1/5/05
Susan 2/5/05
Dale 2/5/05
Dale 2/5/05
Mike 1/5/05

I want to have a formula that will count how many times the name "Dale" in
column A shows up with the date "2/5/05" in column B. I have a summary sheet
and the information is on sheet '2005'! With the example above, my answer
would be 2.

I have tried several formulas including:

=COUNT('2005'!G:G="*Dale*",'2005'!A:A="1/5/05")
=SUMPRODUCT(('2005'!G:G="*Dale*")*('2005'!A:A="2/5/05"))

What am I doing wrong? Thanks for all your help in advance.

 

[JulieD]

Hi Toby

you're on the right track with the SUMPRODUCT function - however, you can't
use full columns with this function - you need to define the range.

=SUMPRODUCT(--('2005'!G1:G17="Dale"),--('2005'!A1:A17=DATEVALUE("2/05/05")))

check out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

for details on the sumproduct function

Cheers
JulieD

 

[Bob Phillips]

FWIW, I always suggest using a date check of

--"2005-05-05" (generically --"yyyy-mm-dd" )

to protect against not working in other countries

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Toby0924]

Thank you for your help!

FWIW, I always suggest using a date check of

--"2005-05-05" (generically --"yyyy-mm-dd" )

to protect against not working in other countries

--

HTH

RP
(remove nothere from the email address if mailing direct)