Sumproduct and list of dates
 

Morning All,
I have a list of dates in a column I need to know how many are from January,
February, March etc.
The formula I am currently using is
=SUMPRODUCT(--(MONTH(Rng)=1)),--(ISNUMBER(Rng)))
this works fine if all the dates are from one year eg all 2004, however I
now have data containing dates from 2004 and 2005.
How can I adapt the formula to distinguish btn Jan 2004 and Jan 2005?
TIA
Ajay

Hi Ajay

One way:

=SUMPRODUCT(--(MONTH(Rng)=1)),--(YEAR(Rng)=2004)),--(ISNUMBER(Rng)))


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Ajay" skrev i en meddelelse
...
Morning All,
I have a list of dates in a column I need to know how many are from
January,
February, March etc.
The formula I am currently using is
=SUMPRODUCT(--(MONTH(Rng)=1)),--(ISNUMBER(Rng)))
this works fine if all the dates are from one year eg all 2004, however I
now have data containing dates from 2004 and 2005.
How can I adapt the formula to distinguish btn Jan 2004 and Jan 2005?
TIA
Ajay

=SUMPRODUCT(--(Year(Rng)=2005), --(MONTH(Rng)=1))

or

=SUMPRODUCT(--(TEXT(rng,"yyyymm")="200501"))

--
HTH

Bob Phillips

"Ajay" wrote in message
...
Morning All,
I have a list of dates in a column I need to know how many are from
January,
February, March etc.
The formula I am currently using is
=SUMPRODUCT(--(MONTH(Rng)=1)),--(ISNUMBER(Rng)))
this works fine if all the dates are from one year eg all 2004, however I
now have data containing dates from 2004 and 2005.
How can I adapt the formula to distinguish btn Jan 2004 and Jan 2005?
TIA
Ajay

If you know the year, then use:
=SUMPRODUCT(--(MONTH(rng)=1),--(YEAR(rng)=2006),--(ISNUMBER(rng)))

- Mangesh


"Ajay" wrote in message
...
Morning All,
I have a list of dates in a column I need to know how many are from
January,
February, March etc.
The formula I am currently using is
=SUMPRODUCT(--(MONTH(Rng)=1)),--(ISNUMBER(Rng)))
this works fine if all the dates are from one year eg all 2004, however I
now have data containing dates from 2004 and 2005.
How can I adapt the formula to distinguish btn Jan 2004 and Jan 2005?
TIA
Ajay

Thanks everyone they all work
Ajay

"Mangesh" wrote:

If you know the year, then use:
=SUMPRODUCT(--(MONTH(rng)=1),--(YEAR(rng)=2006),--(ISNUMBER(rng)))

- Mangesh


"Ajay" wrote in message
...
Morning All,
I have a list of dates in a column I need to know how many are from
January,
February, March etc.
The formula I am currently using is
=SUMPRODUCT(--(MONTH(Rng)=1)),--(ISNUMBER(Rng)))
this works fine if all the dates are from one year eg all 2004, however I
now have data containing dates from 2004 and 2005.
How can I adapt the formula to distinguish btn Jan 2004 and Jan 2005?
TIA
Ajay