SumProduct with dates
 

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

SumProduct with dates
 

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


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)

SumProduct with dates
 

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

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

--

Dave Peterson

SumProduct with dates
 

Spot on, thank you very much, very grateful
--
with kind regards

Spike


"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

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