Help with sumproduct

[deckerp]

Hi experts, I would like to sum tax amounts payable by month.

The values to look up, whereas the range is always row 13 to 73.

• Invoicing date: 04.10.2010
• Tax amount: 54,98 €
• Tax paid: paid (in case that not paid yet the cell remains empty)
• Invoice paid: 12.10.2010

I would like a summary table for tax paid and to be paid:

EG:
Month total tax tax paid
Aug 10 ___ 100,00 € ___ 100,00 €
Sep 10 ___ 200,00 € ___ 88,00 €
Oct 10 ___ 50,00 € ___ 12,00 €
...


My fomula:

=SUMPRODUCT(--(DEBITOREN!$D$13-$D$75>DATE(2010;7;31));--(DEBITOREN!$D$13-$D$75<date(2010;9;1));debitoren!$g$13:$g$75)

returns: #VALUE!

D = invoice dates
G = tax amount
H = tax paid
M = invoice paid

Thanks for your kind help.</date(2010;9;1));debitoren!$g$13:$g$75)

 

[deckerp]

just want to add a question:
Is it possible to make the formula better, that looks if the date range is between two dates or within a specific months?

thanks

<date(2010;9;1));debitoren!$g$13:$g$75)

</date(2010;9;1));debitoren!$g$13:$g$75)

 

[deckerp]

I managed to get a result with this formula:

=SUMPRODUCT(--((DEBITOREN!$J$13:$J$73)>DATE(2010;8;31));--((DEBITOREN!$J$13:$J$73)<DATE(2010;10;1));DEBITOREN!$G$13:$G$73)


<date(2010;10;1));debitoren!$g$13:$g$73)
<date(2010;10;1));debitoren!$g$13:$g$73)
but it produces an error. In my example it adds a tax value from the invoice date 26 august 2010</date(2010;10;1));debitoren!$g$13:$g$73)
</date(2010;10;1));debitoren!$g$13:$g$73)

 

[deckerp]

=SUMPRODUCT(--((DEBITOREN!$J$13:$J$73)>DATE(2010;8;31));--((DEBITOREN!$J$13:$J$73)<DATE(2010;10;1));DEBITOREN!$G$13:$G$73)

Did not previously accept post of formula.

I managed to get a result with this formula:

=SUMPRODUCT(--((DEBITOREN!$J$13:$J$73)>DATE(2010;8;31));--((DEBITOREN!$J$13:$J$73)<date(2010;10;1));debitoren!$g$13:$g$73)


<date(2010;10;1));debitoren!$g$13:$g$73)
<date(2010;10;1));debitoren!$g$13:$g$73)
but it produces an error. In my example it adds a tax value from the invoice date 26 august 2010</date(2010;10;1));debitoren!$g$13:$g$73)

</date(2010;10;1));debitoren!$g$13:$g$73)
</date(2010;10;1));debitoren!$g$13:$g$73)

 

[Dave Patton]

Solutions could include
a) a Pivot Table or
b) Sumif or
c) SumProduct

Edit for your columns, rows, and sheet names.

Excel Workbook
	D	E	F	G	H
1	Date	Tax Amount	Tax Paid
2	Aug 05, 2010	100	100
3	Aug 25, 2010	200	88
4	Sep 14, 2010	50	12
5	Oct 04, 2010	100	50
6	Oct 24, 2010	150
7	Nov 13, 2010	200	50
8
9
10			Data
11	Years	Date	Sum of Tax Amount	Sum of Tax Paid	Sum of Tax Payable
12	2010	Aug	300.00	188.00	112.00
13		Sep	50.00	12.00	38.00
14		Oct	250.00	50.00	200.00
15		Nov	200.00	50.00	150.00
16	Grand Total		800.00	300.00	500.00
17
18
19	Aug 01, 2010		300.00	188.00	112.00
20	Sep 01, 2010		50.00	12.00	38.00
21
3c

Excel 2003

Cell Formulas
Range	Formula
H19	=F19-G19
F19	=SUMIF(D2:D7,">"&D19,E2:E7)-SUMIF(D2:D7,">="&D20,E2:E7)
F20	=SUMPRODUCT(--(D2:D7-DAY(D2:D7)+1=D20),(E2:E7))