I have an excel 2007 workbook that I am tracking invoice payments by supplier, date, and amount. I want to total each amount by supplier and date at the top of my workbook. I have tried using the SUMPRODUCT formula since it worked while tracking a single set of parameters (date and cost), but it doesn't work with more than two or more parameters.
Here is my formula:
SUMPRODUCT(--($B$15:$B$308>=A3,B2),
"--($B$15:$B$308
<A4,C2)"
*($C$15:$C$308))
<A4,C2) doesn't want to post at the end of the second term.
I want the layout to be something like this only larger.The actual formula will be entered in 6+ different cells down for each supplier, and accross for each month. Covering 72 cells with slightly differnt formulas.:
A B C D
2 January February March
3 Supplier 1 $0.00 $0.00 $111.00
4 Supplier 2 $222.00 $0.00 $0.00
5 Supplier 3 $0.00 $0.00 $333.00
14 Payee Date Payment
15 Supplier 2 1-Jan-2011 $111.00
16 Supplier 1 14-Mar-2011 $222.00
17 Supplier 3 4-Mar-2011 $333.00
A3 and A4 represent cells down with my suppliers names. I think that this is one thing causing most of my problems. I tried replacing cell position with supplier and/or month names with no luck.
B2 and C2 represent date names across the top. This will work by itself but not with the parameter of the supplier cells.
$B$15:$B$308 represents the dates entered; $C$15:$C$308 represents the amounts entered. I tried it also with $A$15:$A$308 to represent the Supplier names entered, but it gives me a fault when I add this parameter.
The current formula gives me #Value! or #Name? depending on if I replace cell positions with names or months.
What do I need to do to fix this formula?
Here is my formula:
SUMPRODUCT(--($B$15:$B$308>=A3,B2),
"--($B$15:$B$308
<A4,C2)"
*($C$15:$C$308))
<A4,C2) doesn't want to post at the end of the second term.
I want the layout to be something like this only larger.The actual formula will be entered in 6+ different cells down for each supplier, and accross for each month. Covering 72 cells with slightly differnt formulas.:
A B C D
2 January February March
3 Supplier 1 $0.00 $0.00 $111.00
4 Supplier 2 $222.00 $0.00 $0.00
5 Supplier 3 $0.00 $0.00 $333.00
14 Payee Date Payment
15 Supplier 2 1-Jan-2011 $111.00
16 Supplier 1 14-Mar-2011 $222.00
17 Supplier 3 4-Mar-2011 $333.00
A3 and A4 represent cells down with my suppliers names. I think that this is one thing causing most of my problems. I tried replacing cell position with supplier and/or month names with no luck.
B2 and C2 represent date names across the top. This will work by itself but not with the parameter of the supplier cells.
$B$15:$B$308 represents the dates entered; $C$15:$C$308 represents the amounts entered. I tried it also with $A$15:$A$308 to represent the Supplier names entered, but it gives me a fault when I add this parameter.
The current formula gives me #Value! or #Name? depending on if I replace cell positions with names or months.
What do I need to do to fix this formula?
Last edited:
