sumif with Month() function

  • Thread starter Thread starter HGood
  • Start date Start date
H

HGood

I'm trying to come up with a simple home expense tracker for budgeting. The
four columns I'm using are:
Date, Category, Desc. and Cost.

I want to have a cell (G3) where I can type a month, e.g. "Sep", and then
have it sum all the expenses for that month to see if I'm within budget.

I'm trying to do it with SUMIF with this formula, but it won't work.
=SUMIF(MONTH(B4:B4000),MONTH(G3),E4:E4000). It won't even let me enter this
formula, I get an error message.

Can someone tell me what is wrong with this formula, or a better way to sum
a month's worth of expenses?

Thanks,

Harold
 
Harold, try this, with 9 in G3

=SUMPRODUCT((MONTH(B4:B4000)=G3)*(E4:E4000))

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 2003
** remove news from my email address to reply by email **
 
I'm trying to come up with a simple home expense tracker for budgeting. The
four columns I'm using are:
Date, Category, Desc. and Cost.

I want to have a cell (G3) where I can type a month, e.g. "Sep", and then
have it sum all the expenses for that month to see if I'm within budget.

I'm trying to do it with SUMIF with this formula, but it won't work.
=SUMIF(MONTH(B4:B4000),MONTH(G3),E4:E4000). It won't even let me enter this
formula, I get an error message.

Can someone tell me what is wrong with this formula, or a better way to sum
a month's worth of expenses?

Thanks,

Harold
Click to expand...

If you want ALL February's, regardless of the year, try:

=SUMPRODUCT(--(MONTH(B4:B4000)=G3,E4:E4000)

where G3 contains the month *number*. If you want to put the
month name in, instead, it is possible, but I will leave you with that
exercise.

If, on the other hand, you only want the February data from a given year, then
try:

=SUMIF(B4:B1000,">"&DATE(YEAR(G3),MONTH(G3),0),E4:E4000) -
SUMIF(B4:B1000,">"&DATE(YEAR(G3),MONTH(G3)+1,0),E4:E4000)

with some date in the month of interest in G3.


--ron
 
Great help guys,

This is exactly what I needed.

Now I'm curious, what does the "--" do in this formula? I've never seen
that before.

Thanks,

Harold
 
Thanks, that's really interesting. Didn't see that in any of my books!!

Great places to learn, you guys are good teachers,

Thanks,

Harold
 
Back
Top