SUMIF with 2 criteria

[elevenaminla]

hey guys...

From a data table I've created, I'm trying to summarize expenses MONTHLY based on:

a - CATEGORY
b - DATE

I currently have DATE, EXPENSE, CATEGORY as column headers. DATE is in actual date (e.g. 03/05/11), expense is number (e.g. $5,000), category is textual (e.g. Marketing).

What I want to do is SUM by category and by date (e.g. sum all Marketing expenses in March 2011).

I tried a Pivot Table but I can't figure out how to just show March 2011 as a header labels instead of actual dates (03/05/11, 03/06/11, etc).

Any thoughts?

Thank you!

 

[Ron Coderre]

It's really helpful if you post the version of Excel that you are using so we can give you appropriate instructions.

 

[elevenaminla]

Sorry, I'm using Office for Mac 2011

 

[texasalynn]

try this

Excel Workbook
	A	B	C	D	E	F
1	Date	Expense	Category
2	3/5/2011	5000	Marketing		Marketing/March 2011	6000
3	2/1/2011	2000	Sales
4	3/1/2011	1000	Marketing
5	1/15/2011	1500	Accounting
6
Sheet2

Excel 2003

Cell Formulas
Range	Formula
F2	=SUMPRODUCT(--(MONTH(A2:A5)=3),--(YEAR(A2:A5)=2011),--(C2:C5="Marketing"),(B2:B5))

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(--(DateRange-DAY(DateRange)+1=E2),--(CategoryRange=F2),ExpenseRange)

where E2 houses a first day date of a relevant month/year pair
and F2 a category of interest like Marketing.

On Excel 2007 or later, try...

=SUMIFS(ExpenseRange,DateRange,">="&E2,DateRange,"<="&EOMONTH(E2,0),CategoryRange, F2)

 

[elevenaminla]

Thanks guys!!! Got it to work using the older Excel formula. Wanted to get it a bit more automated with SUMIFS but wasn't working right.

Thanks once again.. this board is a life saver

 

[Aladin Akyurek]

Thanks guys!!! Got it to work using the older Excel formula. Wanted to get it a bit more automated with SUMIFS but wasn't working right.

Thanks once again.. this board is a life saver

How did you try SumIfs?

 

[elevenaminla]

This is the formula I tried

=SUMIFS(ExpenseRange,DateRange,">&F2,DateRange,"<=""&EOMONTH(F2,0),CategoryRange,D3)

Where F2 = 12/1/2010
And D3 = Marketing

 

[Aladin Akyurek]

This is the formula I tried

=SUMIFS(ExpenseRange,DateRange,">&F2,DateRange,"<=""&EOMONTH(F2,0),CategoryRange,D3)

Where F2 = 12/1/2010
And D3 = Marketing

Did you substitute the right ranges for the placeholders like ExpenseRange, etc?

 

[elevenaminla]

Yep, e.g. DateRange is F15:F500