SumProduct with dates

  • Thread starter Thread starter Spike
  • Start date Start date
S

Spike

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
 
Spike said:
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
Click to expand...

Assuming you have dates on sheet 2, formatted to show month and year, you could
use something like this:

=SUMPRODUCT((TEXT(A1,"mmm yy")=TEXT(Sheet1!$A$1:$A$5,"mmm yy"))*
Sheet1!$B$1:$B$5)


If you actually have text on sheet 2, then use this:

=SUMPRODUCT((A1=TEXT(Sheet1!$A$1:$A$5,"mmm yy"))*Sheet1!$B$1:$B$5)
 
With the characters "Jan 08" in A1 (not a date formatted that way):
=sumproduct(--(text(sheet1!a1:a10,"mmm yy")=a$1),(sheet1!b1:b10))

If the cell contained an actual date:
=sumproduct(--(text(sheet1!a1:a10,"mmm yy")=text(a$1,"mmm yy")),(sheet1!b1:b10))
 
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))*Sheet1!$B1:$B5)
 
Back
Top