Counting dates based on certain criteria

  • Thread starter Thread starter steve_sr2
  • Start date Start date
S

steve_sr2

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...
 
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??
 
When I use this, I get an error #VALUE

JMB said:
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??
Click to expand...
 
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"))

---
 
Thanks Max, the option worked...

Max said:
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"))
Click to expand...
 
Back
Top