SUMPRODUCT by Date

C

Curtis

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
 
T

T. Valko

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

Curtis

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

T. Valko

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
 

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

Similar Threads

Dates In Excel 5
Sumproduct giving #Value error 5
Sumproduct in matrix means 3
Sumproduct Help! 3
sum by category, by month 1
Sumproduct neither nor 6
Multiple conditions 4
combine three if functions 3

Top