PDA

View Full Version : Excel Function


jakester
Mar 6, 2010, 07:34 AM
I'm trying to figure out a way to Sum charges based upon date criteria and charge type criteria. Here's my problem.

I keep a spreadsheet for personal finances. I use codes for different charges to easily sum and keep track of categories. Here's what it would look like:

Date Description Amount Code
1/15/10 Groceries $50.00 GROC
1/21/10 Gas $22.50 GAS


At the end of each month, using a SumIf function, I am able to sum all charges based upon the code but I've been starting fresh each month and deleting the previous month data. I'd like to just keep entering the charges into my spreadsheet and roll up the charges for each given month. Can I add another condition to my sumif function that will sum for a given date period (Jan 1 - Jan 31, Feb 1 - Feb 28) based upon that particular code?

Thanks.

JBeaucaire
Mar 6, 2010, 11:04 AM
In place of the SUMIF(), you'll need to construct a SUMPRODUCT() to replace it.


A B C D E G H
1 Date Description Amount Code Start 1/1/2010
2 1/15/2010 Groceries $50.00 GROC End 1/31/2010
3 1/21/2010 Gas $22.50 GAS
4 1/25/2010 Groceries $50.00 GROC GROC $100.00
5 1/30/2010 Gas $22.50 GAS GAS $45.00
6 2/13/2010 Groceries $50.00 GROC
7 2/13/2010 Gas $22.50 GAS

The formula for H4 would be:
=SUMPRODUCT(($D$2:$D$100=G4) * ($A$2:$A$100>=$H$1) * ($A$2:$A$100<=$H$2) * $C$2:$C$100)

... copy that formula downward, it will adjust itself for each category.

jakester
Mar 7, 2010, 09:17 AM
In place of the SUMIF(), you'll need to construct a SUMPRODUCT() to replace it.


A B C D E G H
1 Date Description Amount Code Start 1/1/2010
2 1/15/2010 Groceries $50.00 GROC End 1/31/2010
3 1/21/2010 Gas $22.50 GAS
4 1/25/2010 Groceries $50.00 GROC GROC $100.00
5 1/30/2010 Gas $22.50 GAS GAS $45.00
6 2/13/2010 Groceries $50.00 GROC
7 2/13/2010 Gas $22.50 GAS

The formula for H4 would be:
=SUMPRODUCT(($D$2:$D$100=G4) * ($A$2:$A$100>=$H$1) * ($A$2:$A$100<=$H$2) * $C$2:$C$100)

...copy that formula downward, it will adjust itself for each category.

Thanks, JB... it worked great!