Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT by Date
I need to calcualte the volume totals by month. The data is compiled daily in
column J of sheet 2 (data) =SUMPRODUCT(--('Feb-Aug09 Data'!$K$3:$K$1406=Summary!$A6)*--('Feb-Aug09 Data'!$I$3:$I$1406=C3)*('Feb-Aug09 Data'!$C$3:$C$1406)) Row 3 C thru N in my summary sheet contains the months in format mm/dd/yyyy. Column I in my Data sheet contains all the dates asme format I need to calcualte the volumes by month as identifed in sheet 2 Hope that makes sense Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT by Date
Try it like this:
=SUMPRODUCT(--('Feb-Aug09 Data'!$K$3:$K$1406=Summary!$A6),--('Feb-Aug09 Data'!$I$3:$I$1406=C3),'Feb-Aug09 Data'!$C$3:$C$1406) -- Biff Microsoft Excel MVP "Curtis" wrote in message ... I need to calcualte the volume totals by month. The data is compiled daily in column J of sheet 2 (data) =SUMPRODUCT(--('Feb-Aug09 Data'!$K$3:$K$1406=Summary!$A6)*--('Feb-Aug09 Data'!$I$3:$I$1406=C3)*('Feb-Aug09 Data'!$C$3:$C$1406)) Row 3 C thru N in my summary sheet contains the months in format mm/dd/yyyy. Column I in my Data sheet contains all the dates asme format I need to calcualte the volumes by month as identifed in sheet 2 Hope that makes sense Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT by Date
Sheet 1 = Summary Sheet
Column A rows 7 thru 18 contains Supplier Column C thru N Row 4 contains the date by month The data to be populated exists in column C thru N rows 7 thru 18 Sheet 2 = Data sheet (contains the info required for sheet 1 Column C contains the quanity Column I contains the date (Can be any day of any month of any year) Column K contains the vendor I need a formula in my summary sheet that will look at my data sheet and tell me the quanity by month by vendor Thanks ce "T. Valko" wrote: Try it like this: =SUMPRODUCT(--('Feb-Aug09 Data'!$K$3:$K$1406=Summary!$A6),--('Feb-Aug09 Data'!$I$3:$I$1406=C3),'Feb-Aug09 Data'!$C$3:$C$1406) -- Biff Microsoft Excel MVP "Curtis" wrote in message ... I need to calcualte the volume totals by month. The data is compiled daily in column J of sheet 2 (data) =SUMPRODUCT(--('Feb-Aug09 Data'!$K$3:$K$1406=Summary!$A6)*--('Feb-Aug09 Data'!$I$3:$I$1406=C3)*('Feb-Aug09 Data'!$C$3:$C$1406)) Row 3 C thru N in my summary sheet contains the months in format mm/dd/yyyy. Column I in my Data sheet contains all the dates asme format I need to calcualte the volumes by month as identifed in sheet 2 Hope that makes sense Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT by Date
Try this entered in C7:
=SUMPRODUCT(--(Sheet2!$K$2:$K$15=$A7),--(TEXT(Sheet2!$I$2:$I$15,"myyyy")=TEXT(Sheet1!C$4," myyyy")),Sheet2!$C$2:$C$15) Adjust the ranges to suit. Copy across to N7 then down to C18:N18 -- Biff Microsoft Excel MVP "Curtis" wrote in message ... Sheet 1 = Summary Sheet Column A rows 7 thru 18 contains Supplier Column C thru N Row 4 contains the date by month The data to be populated exists in column C thru N rows 7 thru 18 Sheet 2 = Data sheet (contains the info required for sheet 1 Column C contains the quanity Column I contains the date (Can be any day of any month of any year) Column K contains the vendor I need a formula in my summary sheet that will look at my data sheet and tell me the quanity by month by vendor Thanks ce "T. Valko" wrote: Try it like this: =SUMPRODUCT(--('Feb-Aug09 Data'!$K$3:$K$1406=Summary!$A6),--('Feb-Aug09 Data'!$I$3:$I$1406=C3),'Feb-Aug09 Data'!$C$3:$C$1406) -- Biff Microsoft Excel MVP "Curtis" wrote in message ... I need to calcualte the volume totals by month. The data is compiled daily in column J of sheet 2 (data) =SUMPRODUCT(--('Feb-Aug09 Data'!$K$3:$K$1406=Summary!$A6)*--('Feb-Aug09 Data'!$I$3:$I$1406=C3)*('Feb-Aug09 Data'!$C$3:$C$1406)) Row 3 C thru N in my summary sheet contains the months in format mm/dd/yyyy. Column I in my Data sheet contains all the dates asme format I need to calcualte the volumes by month as identifed in sheet 2 Hope that makes sense Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct? by date | Excel Discussion (Misc queries) | |||
sumproduct with more than one date | Excel Discussion (Misc queries) | |||
Sumproduct by date | Excel Discussion (Misc queries) | |||
SUMPRODUCT + DATE = 0 | Excel Worksheet Functions | |||
SUMPRODUCT + DATE = 0 | Excel Worksheet Functions |