SumIf by Date?

[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

 

[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

 

[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

 

[Elkar]

This should work:

=SUMPRODUCT(--(MONTH(A1:A100)=5,B1:B100)

HTH
Elkar

 

[Elkar]

This should work:

=SUMPRODUCT(--(MONTH(A1:A100)=5,B1:B100)

HTH
Elkar

 

[JM_Brazil]

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

Many thanks from Brazil,

JM

 

[JM_Brazil]

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

Many thanks from Brazil,

JM

 

[JM_Brazil]

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

 

[JM_Brazil]

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

 

[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

 

[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

 

[JM_Brazil]

Thanks Elkar, your help is greatly appriciated. Best regards,
JM

 

[JM_Brazil]

Thanks Elkar, your help is greatly appriciated. Best regards,
JM