Complicated if/then formula with date ranges

[txkaratemama]

Hello everyone! Thanks in advance for any help you can offer me.

I'm trying to create a budget that autopopulates based on info entered into my checkbook. I have categories attached to each checkbook entry. I want the budget to create a sum based on both the category and date.

So for each entry, my checkbook has a column for the category (gas, grocery, utilities, etc.) and a column for date, in addition to the debit/credit column. I want my budget column for each category to look at the category, date, and debit columns from my checkbook and create a sum like this:

Add the debit columns IF category=gas AND date=1/1/12-1/31/12.

And I want to do this for each category for each month. I have a different budget for every month.

Any ideas? Thanks so much!

 

[T. Valko]

Hello everyone! Thanks in advance for any help you can offer me.

I'm trying to create a budget that autopopulates based on info entered into my checkbook. I have categories attached to each checkbook entry. I want the budget to create a sum based on both the category and date.

So for each entry, my checkbook has a column for the category (gas, grocery, utilities, etc.) and a column for date, in addition to the debit/credit column. I want my budget column for each category to look at the category, date, and debit columns from my checkbook and create a sum like this:

Add the debit columns IF category=gas AND date=1/1/12-1/31/12.

And I want to do this for each category for each month. I have a different budget for every month.

Any ideas? Thanks so much!

What version of Excel are you using?

 

[txkaratemama]

XP

 

[T. Valko]

XP

Try something like this...

Data:

Book1
	A	B	C
1	Date	Category	Debit
2	12/27/2011	Grocery	48
3	12/28/2011	Gas	73
4	12/29/2011	Cable	97
5	12/30/2011	Clothes	84
6	12/31/2011	Gas	48
7	1/1/2012	Cash	14
8	1/2/2012	Cash	15
9	1/3/2012	Gas	73
10	1/4/2012	Grocery	27
11	1/5/2012	Phone	12
12	1/6/2012	Cash	15
13	1/7/2012	Gas	12
14	1/8/2012	Electric	84
15	1/9/2012	Misc	72
Sheet1

Summary area:

Book1
	E	F	G
1	Month	Category	Total
2	Jan 2012	Gas	85
Sheet1

Cell E2 contains the date 1/1/2012 formatted to display as mmm yyyy.

This formula entered in G2:

=SUMPRODUCT(--(TEXT(A$2:A$15,"mmmyyyy")=TEXT(E2,"mmmyyyy")),--(B$2:B$15=F2),C$2:C$15)