SumIf by Date?
 

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
--
I have chosen not to write anything in this space.

SumIf by Date?
 

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


"JM_Brazil" wrote:

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
--
I have chosen not to write anything in this space.

SumIf by Date?
 

This should work:

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

HTH
Elkar


"JM_Brazil" wrote:

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
--
I have chosen not to write anything in this space.

SumIf by Date?
 

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

Many thanks from Brazil,

JM
--
I have chosen not to write anything in this space.


"Jacob Skaria" wrote:

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


"JM_Brazil" wrote:

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
--
I have chosen not to write anything in this space.

SumIf by Date?
 

This looks good Elkar, but it didn't work for me. Why the 2 hyphens after the
first parenthasis?
--
I have chosen not to write anything in this space.


"Elkar" wrote:

This should work:

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

HTH
Elkar


"JM_Brazil" wrote:

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
--
I have chosen not to write anything in this space.

SumIf by Date?
 

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" wrote:

This looks good Elkar, but it didn't work for me. Why the 2 hyphens after the
first parenthasis?
--
I have chosen not to write anything in this space.


"Elkar" wrote:

This should work:

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

HTH
Elkar


"JM_Brazil" wrote:

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
--
I have chosen not to write anything in this space.

SumIf by Date?
 

Thanks Elkar, your help is greatly appriciated. Best regards,
JM
--
I have chosen not to write anything in this space.


"Elkar" wrote:

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" wrote:

This looks good Elkar, but it didn't work for me. Why the 2 hyphens after the
first parenthasis?
--
I have chosen not to write anything in this space.


"Elkar" wrote:

This should work:

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

HTH
Elkar


"JM_Brazil" wrote:

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
--
I have chosen not to write anything in this space.