Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
=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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT and format problems (2) | Excel Discussion (Misc queries) | |||
Sumproduct on filtered cells | Excel Worksheet Functions | |||
SUMPRODUCT ON TIMES | Excel Worksheet Functions | |||
SUMPRODUCT ON DATES | Excel Worksheet Functions | |||
SUMIF or SUMPRODUCT or something else? | Excel Worksheet Functions |