Help with SumIf with two conditions

**xlwho** · 01-07-2007, 11:41 AM

I searched the forum and found some help with a personal expense tracking spreadsheet I'm creating for myself, but I need to add a second condition to the SumIf command. Is this possible or is there another way to accomplish this?

My spreadsheet is a very basic expense log with 4 columns: A=Date, B=Amount, C=Category (Gas, Food, Tuition, etc.) & D=Type (Cash, Check, VISA, etc.). To summarize the expenses, I am trying to create a table with the categories (going down) and the months (going across). To populate the first cell in the table, I need to sum all amounts spent on Gas in January. The formula =SUMIF(C:C,"Gas",B:B) allows me to sum all amounts spent on gas, but how can I add the second condition (spent in January) in the same cell formula? Once I figure this out, I can place similar formulas in the remaining table cells.

Thanks for your time!

**VBA Noob** · 01-07-2007, 11:48 AM

Hi,

Try Sumproduct.

Note you can't use the whole column like sumif.

Also a warning. It will sum any year with month of Jan

=SUMPRODUCT(--(TEXT($A$2:$A$1000,"mmm")="Jan")*($C$2:$C$1000="Gas")*($B$2:$B$1000))

or

=SUMPRODUCT(--(MONTH($A$2:$A$1000)=1)*($C$2:$C$1000="Gas")*($B$2:$B$1000)))

This link explains more about sumproduct

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

VBA Noob

**VBA Noob** · 01-07-2007, 12:00 PM

Hi again.

Here's another way

=SUMPRODUCT(--($A$2:$A$1000>=DATEVALUE("01/01/2007"))*($A$2:$A$1000<=DATEVALUE("31/01/2007"))*($C$2:$C$1000="Gas")*($B$2:$B$1000))

VBA Noob

**xlwho** · 01-07-2007, 01:05 PM

I used the date range example and it works great! For some reason, the MONTH($A$2:$A$1000)=1 method didn't work when I changed the values for subsequent months (2, 3, 4) even when I changed date values in the log to be dates in Feb, March, etc. with matching categories. Strange.

Thanks for your suggestions...I appreciate it!