Count with multiple conditions

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.

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

"Toby0924" <[email protected]> wrote in message
news:[email protected]...
> 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.

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)


"JulieD" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
> "Toby0924" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>

Thank you for your help!

"Bob Phillips" wrote:

> 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)
>
>
> "JulieD" <[email protected]> wrote in message
> news:%[email protected]...
> > 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
> >
> > "Toby0924" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> >
> >
>
>
>