Counting dates based on certain criteria

[steve_sr2]

I have a col of dates (D1250) 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...

 

[JMB]

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

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

 

[steve_sr2]

When I use this, I get an error #VALUE

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

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

 

[Max]

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(D1250,"mmmyyyy")="Aug2007"))

---

 

[steve_sr2]

Thanks Max, the option worked...

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(D1250,"mmmyyyy")="Aug2007"))

 

[Max]

welcome, glad it helped