Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif date is greater than or equal chosen date | Excel Discussion (Misc queries) | |||
Sumif....date in table is greater than compared to date | Excel Discussion (Misc queries) | |||
sumif < date | Excel Worksheet Functions | |||
SUMIF for date | Excel Discussion (Misc queries) | |||
SUMIF within date range as a function of today()'s date | Excel Worksheet Functions |