sumif with Month() function

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
 
P

Paul B

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 **
 
R

Ron Rosenfeld

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
 
H

HGood

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
 
H

HGood

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

if and sumif 3
Multiple Sumif with concatenation 5
SUMIF with NAME RANGE 1
travel expenses by month 1
Using SUMIF with AND 2
SUMIF Help 1
SumIF with 2 Conditions 6
Use of Month() within SumIF() 1

Top