Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct with dates | New Users to Excel | |||
Sumproduct and dates | New Users to Excel | |||
sumproduct between dates | Excel Worksheet Functions | |||
SUMPRODUCT ON DATES | Excel Worksheet Functions | |||
sumproduct between dates | Excel Worksheet Functions |