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!

 

[Aladin Akyurek]

Aladin, I don't know what I'm doing wrong in regards to F2. I have the date entered 12/1/2010... have tried 1/12/2010. Not sure what is wrong.

The formatting is set to Date in "Format Cells" options

In F2 enter:

1-Dec-11

In F1 enter:

=ISNUMBER(F2)

If you see TRUE appear in F1, we can assume that you have succeeded
to have a first day date in F2, which is December 1st, 2011.

Otherwise try:

=SUMIFS($J$15:$J$100,$F$15:$F$100,">="&DATE(2011,12,1),$F$15:$F$100,"<="&EOMONTH(DATE(2011,12,1),0),$K$15:$K$100,$D3)

This should give you a total for Dec-11 regarding the value in D3.

 

[elevenaminla]

Aladin, I created a sheet from scratch and the formula worked! Have no idea why it wasn't working originally, but I'll play with it or rebuild data.

Thank you once again so so much!!!

 

[elevenaminla]

Seems like it must be the Validation list not matching the Categories in the summary table. Ugh

 

[elevenaminla]

Sorry actually the dates in the table are not numbers! When I ran the ISNUMBER I'm getting FALSE.

I tried to change the cell format, but they aren't changing format.

 

[elevenaminla]

Sorry about another post, but I fixed it!

Everything is working smoothly. Thanks again Aladin!

 

[snoopyhr]

If you are still interested in utilizing pivot table, right click on the date field, select group and then MONTH, your header will show the whole month consolidated by category

Excel Workbook
	A	B	C
1	Date	Expense	Category
2	3/10/2011	500	Marketing
3	3/4/2011	230	Sales
4	3/15/2011	450	Finance
5	3/2/2011	1000	Finance
6	3/18/2011	1200	Admin
7
8
9	Sum of Expense	Date
10	Category	Mar	Grand Total
11	Admin	1200	1200
12	Finance	1450	1450
13	Marketing	500	500
14	Sales	230	230
15	Grand Total	3380	3380
Sheet3

Just a suggestion,

 

[elevenaminla]

thanks snoopyhr!!!

 

[Aladin Akyurek]

Sorry about another post, but I fixed it!

Everything is working smoothly. Thanks again Aladin!

Glad to hear that. Thanks for providing feedback.