Counting dates based on certain criteria
 

I have a col of dates (D1:D250) and another column (B1:B250) with 3 different
options. I want to know how many times option 1 happened when the month is
Jan, Fec, etc.
option A 5/8/2007
option B 4/10/2007
option B 5/11/2007

Thanks in advance for you help...

Counting dates based on certain criteria
 

For OptionA and January, try:
=Sumproduct(--(B1:B250="OptionA"), --(Month(D1:D250)=1))

do you need to differentiate between Jan 2007 and Jan 2008 or just match to
Jan regardless of the year??


"steve_sr2" wrote:

I have a col of dates (D1:D250) and another column (B1:B250) with 3 different
options. I want to know how many times option 1 happened when the month is
Jan, Fec, etc.
option A 5/8/2007
option B 4/10/2007
option B 5/11/2007

Thanks in advance for you help...

Counting dates based on certain criteria
 

When I use this, I get an error #VALUE

"JMB" wrote:

For OptionA and January, try:
=Sumproduct(--(B1:B250="OptionA"), --(Month(D1:D250)=1))

do you need to differentiate between Jan 2007 and Jan 2008 or just match to
Jan regardless of the year??


"steve_sr2" wrote:

I have a col of dates (D1:D250) and another column (B1:B250) with 3 different
options. I want to know how many times option 1 happened when the month is
Jan, Fec, etc.
option A 5/8/2007
option B 4/10/2007
option B 5/11/2007

Thanks in advance for you help...

Counting dates based on certain criteria
 

When I use this, I get an error #VALUE

That usually means you have #VALUE! error value(s) within your data, either
in col B and/or col D, or col D might contain some cells with text (instead
of real dates/numbers). Check it and clear these cells.

Above said, perhaps a more unambiguous/robust summation to try, as hinted in
JMB's question to you would be something like this:
=SUMPRODUCT(--(B1:B250="Option A"), --(TEXT(D1:D250,"mmmyyyy")="Aug2007"))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Counting dates based on certain criteria
 

Thanks Max, the option worked...

"Max" wrote:

When I use this, I get an error #VALUE

That usually means you have #VALUE! error value(s) within your data, either
in col B and/or col D, or col D might contain some cells with text (instead
of real dates/numbers). Check it and clear these cells.

Above said, perhaps a more unambiguous/robust summation to try, as hinted in
JMB's question to you would be something like this:
=SUMPRODUCT(--(B1:B250="Option A"), --(TEXT(D1:D250,"mmmyyyy")="Aug2007"))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Counting dates based on certain criteria
 

welcome, glad it helped
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"steve_sr2" wrote in message
...
Thanks Max, the option worked...