SumProduct with dates


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
 
Ad

Advertisements

G

Glenn

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

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)
 
D

Dave Peterson

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))
 
Ad

Advertisements

S

Shane Devenshire

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top