Help SUM by date and category

I am working on creating a ledger for myself. I am having some issues with SUMIF, SUMIFS, and SUMPRODUCT. I hope you can help me.

In my ledger example, each transaction has 3 types of data: Date, Category and Amount (A, B, C). I want to be able to sort and auto-sum by week and category, then add the amount to the corresponding column (G, H, I).

The only formula I have been able to get working is =SUMIF(B:B, G1, C:C). This is fine for simply looking at the category, but I cannot automatically filter by date. I have tried following the template example given by Excel for SUMIFS and SUMPRODUCT, but to no avail. It seems like they don't like the repeated reference to column A, checking to see if it's greater or equal to E and less than or equal to F.

Any Ideas?

Thanks for your help!

Try in G2:

=SUMPRODUCT(--($B$2:$B$11=G$1),--($A$2:$A$11>=$E2),--($A$2:$A$11<=$F2),$C$2:$C$11)

copied down and across the matrix.

or in XL2007



=SUMIFS($C$2:$C$11,$B$2:$B$11,G$1,$A$2:$A$11,">="&$E2,$A$2:$A$11,"<="&$F2)