Formula help

[Guest]

Hi, All.

My data table has the following key fields:

A:A End of Month Date mm/dd/yyyy
C:C Amount
F:F Code

My report, starting in another worksheet with Codes in D and End of Month
Dates in E1 - P1, should total the code entries by the months. I'm thinking
it's a SUMPRODUCT() solution, but can't get there on my own.

TIA

 

[Guest]

Put this in D2 and copy across and down:

=SUMPRODUCT((E$1=Sheet2!$A$1:$A$100)*($D2=Sheet2!$F$1:$F$100)*Sheet2!$B$1:$B$100)

Change Sheet2 to whatever it needs to be.

HTH
Jason
Atlanta, GA

 

[Guest]

Jim,

Let me see if I have this right - your data table has "End of Month Date" in
column A, "Amount" in column C, and some sort of code in column "F", and then
on another sheet you're creating a summary table with the codes in column D
and end of month dates in row 1?

Try this in cell E2 and copy it to fill out the rest of the table (I'm
assuming your data worksheet is called "Data" and your summary table
worksheet is called "summary" - if they're called something different, then
substitute accordingly):


=SUMPRODUCT((Data!$F:$F=$D2)*(Data!$A:$A=E$1)*(Data!$C$:$C))

I think this will work. Frank Kabel showed me this method a little while ago.

Eric