SumIf by Date?

J

JM_Brazil

Is there an easy way to sumif row entries based on dates, without having to
create an additional column for an additional formula? (i.e. month())

In other words...
I have several large ongoing database, and would like to sum sales figures
by month. The information I want to use as a criteria is the Close Date,
which is dd/mm/yy. I would normally insert an additional column adjacent to
the Close Date column, do a month(), then sumif by month # in this new
colomn. Is there an easier way to do this? I really don't want to have to
include an additional column, and for some reason, I'm not 100% on the
accuracy of this method. Am open to guidance by Those Who Know on this forum!
Muito thanks,
JM_Brazil
 
J

Jacob Skaria

The below will return the sum of values in ColB for all date entries in Col A
for the month of May (05)

=SUM(IF(MONTH(A1:A100)=5,B1:B100))

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in the Formula Bar you can notice the curly braces at both ends
"{=<formula>}"

If this post helps click Yes
 
J

JM_Brazil

FANTASTIC!
Thanks Jacob, I'm pretty new to Array formulas, but this solved my problem.

Many thanks from Brazil,

JM
 
J

JM_Brazil

This looks good Elkar, but it didn't work for me. Why the 2 hyphens after the
first parenthasis?
 
E

Elkar

Not sure why it wouldn't work for you. Obviously, you'd need to change the
cell references to match your own. And I just used 5 to represent May as the
month for an example.

The purpose of the -- is to convert the results of the MONTH()=5 expression
from True/False to a number which SUMPRODUCT can then use. Basically, its
just multiplying True/False by -1 twice.

The solution that Jacob Skaria gave you will work as well, but has the
disadvantage of being an array formula. Array formulas are slower to
calculate (only a big deal if you have many) and must be committed with
Ctrl-Shift-Enter instead of just Enter. Plus, in my opinion, SUMPRODUCT just
offers a lot more flexibility (dealing with multiple criteria).

HTH
Elkar
 

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

Top