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]

Yep, e.g. DateRange is F15:F500

If F2 is 1-Dec-11 (a first day date) and the real ranges are substitute
for the placeholders, the SumIfs formula should work.

 

[elevenaminla]

Here is a screenshot of how I set it up. For the month of Nov 10 I used the Excel 2003 version and it works... for Dec 10, I'm trying the SUMIFS

http://dl.dropbox.com/u/7939477/Screen shot 2011-05-09 at 9.44.34 PM.png

 

[Aladin Akyurek]

Here is a screenshot of how I set it up. For the month of Nov 10 I used the Excel 2003 version and it works... for Dec 10, I'm trying the SUMIFS

http://dl.dropbox.com/u/7939477/Screen%20shot%202011-05-09%20at%209.44.34%20PM.png

If F2 is housing 1-Dec-10, formatted display Dec-10...

=SUMIFS($J$15:$J$100,$F$15:$F$100,">="&F$2,$F$15:$F$100,"<="&EOMONTH(F$2,0),$K$15:$K$100,$D3)

which is copied down, wpuld yield the desired total amounts.

Would you also post the SumProduct version you use for the same sample?

 

[elevenaminla]

Aladin, here is the SUMPRODUCT formula I'm using

=SUMPRODUCT(--(MONTH(F15:F1003)=11),--(YEAR(F15:F1003)=2010),--(K15:K1003="Computers & Phones"),(J15:J1003))

Not sure why SUMIFS isn't working.

Thanks for your help on this!!!

 

[Aladin Akyurek]

Aladin, here is the SUMPRODUCT formula I'm using

=SUMPRODUCT(--(MONTH(F15:F1003)=11),--(YEAR(F15:F1003)=2010),--(K15:K1003="Computers & Phones"),(J15:J1003))

Not sure why SUMIFS isn't working.

Thanks for your help on this!!!

What do you get with:

=ISNUMBER(F2)

entered in some unused cell?

 

[elevenaminla]

I get TRUE

 

[Aladin Akyurek]

I get TRUE

What does this

=TEXT(F2,"00000")

return?

 

[elevenaminla]

39051

 

[Aladin Akyurek]

39051

This is not a first day date.

If you want to do SumIfs for the month of say December 2011, then enter
in F2 the first day date of this month/year pair. That is:

1-Dec-11

You can format F2 to display Dec-11 if you so wish.

If F2 is correctly set up:

=SUMIFS($J$15:$J$100,$F$15:$F$100,">="&F$2,$F$15:$F$100,"<="&EOMONTH(F$2,0),$K$15:$K$100,$D3)

will simply work just like the SumProduct formula does.

 

[elevenaminla]

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