Sounds like a job for SUMPRODUCT but note you cannot reference the
entire column using this function.

Assume that A1 is the date you are using as criteria and A2 is the
value you are trying to find in Col F.

=SUMPRODUCT(--(C1:C10=A1)*(--(F1:F10=A2)))

How does this work? C1:C10=A1 creates an array of ten TRUE or FALSE
values where the "--" turns them into 1 for TRUE and 0 for FALSE.
F1:F10=A2 does the same for the other range. SUMPRODUCT multiples each
pair of 1's and 0's then adds them up. So if the first pair (C1 and
F1) evaluate as FALSE and TRUE, the results is 0 x 1 or 0. If the
second pair are both TRUE the result would be 1 x 1 or 1. Add up all
of the ones and you'll have your count.

- John
www.JohnMichl.com

Hey John, see Bob's solution below - you can reference the entire column.

MAC

"John Michl" wrote:

Sounds like a job for SUMPRODUCT but note you cannot reference the
entire column using this function.

Assume that A1 is the date you are using as criteria and A2 is the
value you are trying to find in Col F.

=SUMPRODUCT(--(C1:C10=A1)*(--(F1:F10=A2)))

How does this work? C1:C10=A1 creates an array of ten TRUE or FALSE
values where the "--" turns them into 1 for TRUE and 0 for FALSE.
F1:F10=A2 does the same for the other range. SUMPRODUCT multiples each
pair of 1's and 0's then adds them up. So if the first pair (C1 and
F1) evaluate as FALSE and TRUE, the results is 0 x 1 or 0. If the
second pair are both TRUE the result would be 1 x 1 or 1. Add up all
of the ones and you'll have your count.

- John
www.JohnMichl.com

Bob is not referencing the entire column he is referencing the range
from row 2 through row 200. The entire column would be represented by
$C:$C. This is not permitted in SUMPRODUCT.

- John

I have a similar problem but I am not familiar with SUMPRODUCT.
I have a range of dates and I want to get a count of cells by month. (How
many January, February, etc.)

So, if A1:A5 is

1/2/2006
2/2/2006
3/2/2006
3/5/2006
4/2/2006


....and if I'm looking for the number of dates in March I want to...

COUNTIF(A1:A5, = "3/1/2006" AND < "4/1/2006")

(...but of course this formula doesn't work.)

So, how would I do it?

Thanks,
Scott

"John Michl" wrote in message
oups.com...
Sounds like a job for SUMPRODUCT but note you cannot reference the
entire column using this function.

Assume that A1 is the date you are using as criteria and A2 is the
value you are trying to find in Col F.

=SUMPRODUCT(--(C1:C10=A1)*(--(F1:F10=A2)))

How does this work? C1:C10=A1 creates an array of ten TRUE or FALSE
values where the "--" turns them into 1 for TRUE and 0 for FALSE.
F1:F10=A2 does the same for the other range. SUMPRODUCT multiples each
pair of 1's and 0's then adds them up. So if the first pair (C1 and
F1) evaluate as FALSE and TRUE, the results is 0 x 1 or 0. If the
second pair are both TRUE the result would be 1 x 1 or 1. Add up all
of the ones and you'll have your count.

- John
www.JohnMichl.com

=countif(a1:a5,"="&date(2006,3,1)) - countif(a1:a5,"="&date(2006,4,1))

is one way.

Scott Lolmaugh wrote:

I have a similar problem but I am not familiar with SUMPRODUCT.
I have a range of dates and I want to get a count of cells by month. (How
many January, February, etc.)

So, if A1:A5 is

1/2/2006
2/2/2006
3/2/2006
3/5/2006
4/2/2006

...and if I'm looking for the number of dates in March I want to...

COUNTIF(A1:A5, = "3/1/2006" AND < "4/1/2006")

(...but of course this formula doesn't work.)

So, how would I do it?

Thanks,
Scott

"John Michl" wrote in message
oups.com...
Sounds like a job for SUMPRODUCT but note you cannot reference the
entire column using this function.

Assume that A1 is the date you are using as criteria and A2 is the
value you are trying to find in Col F.

=SUMPRODUCT(--(C1:C10=A1)*(--(F1:F10=A2)))

How does this work? C1:C10=A1 creates an array of ten TRUE or FALSE
values where the "--" turns them into 1 for TRUE and 0 for FALSE.
F1:F10=A2 does the same for the other range. SUMPRODUCT multiples each
pair of 1's and 0's then adds them up. So if the first pair (C1 and
F1) evaluate as FALSE and TRUE, the results is 0 x 1 or 0. If the
second pair are both TRUE the result would be 1 x 1 or 1. Add up all
of the ones and you'll have your count.

- John
www.JohnMichl.com


--

Dave Peterson

Jeremy,

=SUMPRODUCT(--(AX2:AX61="OPEN"),--(B2:B61=DATE(2005,10,1)),--(B2:B61<=DATE(2005,12,31)))

or put your reference dates in two other cells say F1 & G1

=SUMPRODUCT(--(AX1:AX20="OPEN"),--(B1:B20=F1),--(B1:B20<=G1))

or don't use the "--" at all

=SUMPRODUCT((AX1:AX20="OPEN")*(B1:B20=F1)*(B1:B20 <=G1))

You were putting the "--" after the = sign in your conditions, this is
not necessary. Also, having the dates in " " doesn't seem to work
either.

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=491906

Jeremy,

Sorry, forgot to change the ranges.

=SUMPRODUCT((AX2:AX61="OPEN")*(B2:B61=F1)*(B2:B61 <=G1))

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=491906

Perfect! Works like a charm.
Thanks!!
Scott

"Dave Peterson" wrote in message
...
=countif(a1:a5,"="&date(2006,3,1)) - countif(a1:a5,"="&date(2006,4,1))

is one way.

Scott Lolmaugh wrote:

I have a similar problem but I am not familiar with SUMPRODUCT.
I have a range of dates and I want to get a count of cells by month.
(How
many January, February, etc.)

So, if A1:A5 is

1/2/2006
2/2/2006
3/2/2006
3/5/2006
4/2/2006

...and if I'm looking for the number of dates in March I want to...

COUNTIF(A1:A5, = "3/1/2006" AND < "4/1/2006")

(...but of course this formula doesn't work.)

So, how would I do it?

Thanks,
Scott

"John Michl" wrote in message
oups.com...
Sounds like a job for SUMPRODUCT but note you cannot reference the
entire column using this function.

Assume that A1 is the date you are using as criteria and A2 is the
value you are trying to find in Col F.

=SUMPRODUCT(--(C1:C10=A1)*(--(F1:F10=A2)))

How does this work? C1:C10=A1 creates an array of ten TRUE or FALSE
values where the "--" turns them into 1 for TRUE and 0 for FALSE.
F1:F10=A2 does the same for the other range. SUMPRODUCT multiples each
pair of 1's and 0's then adds them up. So if the first pair (C1 and
F1) evaluate as FALSE and TRUE, the results is 0 x 1 or 0. If the
second pair are both TRUE the result would be 1 x 1 or 1. Add up all
of the ones and you'll have your count.

- John
www.JohnMichl.com


--

Dave Peterson