SumProduct with dates
If your second sheet has label entries Jan 08 then you can use
=SUMPRODUCT(--(MONTH(Sheet1!$A1:$A5)=MONTH(E1)),Sheet1!$B1:$B5)
and if the dates are entered as actual dates but formatted to show Jan 08
you can use - the same formula!
Another way to write these is
=SUMPRODUCT((MONTH(Sheet1!$A1:$A5)=MONTH(E1))*Shee t1!$B1:$B5)
--
If this helps, please click the Yes button.
Cheers,
Shane Devenshire
"Spike" wrote:
I have column A on sheet 1 with dates from 1/1/2008 to present and column B
with values. On sheet 2 i have across row 1 all the months(with years) ie,
Jan 08, Feb 08 .... to Jul 09. i would like to pull out all the values for
each monthly period and put them in each column on sheet 2.
I think SumProduct is what i should be using but cannot get it to work; any
advice will be very gratefully received
col A Value
1/1/08 124.77
5/1/08 565.77
28/1/08 784.99
2/2/08 99.44
4/2/08 444.22
etc etc
col A Col B Col C ColD
Jan 08 Feb 08 Mar 08 Apr o8 etc to Jul 09
--
with kind regards
Spike
|