SUMIFS by month up to a dynamic date

[JohnZ1156]

I have a table of data. Let's say Column A are dates (workdays or M-F), and Column B are values.
I'd like to get a sum for each month up to a specific date.

For example: If I enter 3/18/22 into a "dynamic" cell, I would like my January and February totals to show the whole months, but I would only like to see a total amount for March from 3/1/22 to 3/18/22.
Regardless if today is August 28th. I'd like to be able to change the date in the "dynamic" cell so I can get a total for the month in the "dynamic" cell from the beginning of that month to that date.

I have a SUMIFS formula that shows me monthly totals, but if always shows me totals for the entire months.
I'd like to be able to "stop" the total at a dynamic date within any given month.

Here is the formula that I currently have.
=SUMIFS(Table4[Flux 2],Table4[Date],">="&$A47,Table4[Date],"<="&EOMONTH($A47,0))

I hope I wasn't too redundant.
Thanks in advance for your help.
John

 

[SanjayGMusafir]

Try this -

=SUMIFS(Table4[Flux 2],Table4[Date],">"&EOMONTH($A47,-3),Table4[Date],"<="&EOMONTH($A47,-1))

 

[JohnZ1156]

Try this -

=SUMIFS(Table4[Flux 2],Table4[Date],">"&EOMONTH($A47,-3),Table4[Date],"<="&EOMONTH($A47,-1))

Where is the dynamic cell reference where I enter the date that changes?
For example, If I only want the month total for July from 7/1/22 to 7/15/22, where do I enter the date 7/15/22?
I don't want it to show me any totals beyond the dynamic date.

 

[SanjayGMusafir]

For example: If I enter 3/18/22 into a "dynamic" cell, I would like my January and February totals to show the whole months, but I would only like to see a total amount for March from 3/1/22 to 3/18/22.

Originally you wanted it to show like above so I generated formula according to that.

In case you want it to be absolute dynamic then add 2 helper cell where you can mention the range for which you want results - one starting one ending.

And modify formula accordingly.

See machine does not judge our mind, it works as per the inputs we give - So find the best ways to tell machine what you want.

In case you need further help feel free to ask.

 

[JohnZ1156]

Originally you wanted it to show like above so I generated formula according to that.

In case you want it to be absolute dynamic then add 2 helper cell where you can mention the range for which you want results - one starting one ending.

And modify formula accordingly.

See machine does not judge our mind, it works as per the inputs we give - So find the best ways to tell machine what you want.

In case you need further help feel free to ask.

Thank you for trying, but either I'm not making myself clear or I don't think you understand.

Let's say it's today, I have a SUMIFS formula for each month of the year. January - December.
Now, let's say that I only want to see totals from the beginning of the year until June 17th.
In a cell, somewhere, I would enter the date 6/17/22.
I would still see the complete monthly totals for Jan - May, but I would only see the June monthly total earned from June 1st through June 17th.

I would not see any totals for any money earned after June 17th.
It would be like I'm going back in time to June 17th.

Then I can change the 6/17/22 date to any other date I wish.

 

[SanjayGMusafir]

In a cell, somewhere, I would enter the date 6/17/22.
I would still see the complete monthly totals for Jan - May, but I would only see the June monthly total earned from June 1st through June 17th

2 things I need -

1. In above you want total from Jan to June 17 or Just for Jun 1 to Jun 17
2. MORE IMPORTANT - The problem could be where and how you are putting formula - So UPLOAD a sample data using XL2BB utility to understand what exactly is going wrong.

The entire exercise is to help you in best possible way.

 

[Fluff]

How about

=SUMIFS(Table4[Flux 2],Table4[Date],">="&$A47,Table4[Date],"<="&MIN(EOMONTH($A47,0),$A$2))

where A2 holds the limiting date.

 

[JohnZ1156]

2 things I need -

1. In above you want total from Jan to June 17 or Just for Jun 1 to Jun 17
2. MORE IMPORTANT - The problem could be where and how you are putting formula - So UPLOAD a sample data using XL2BB utility to understand what exactly is going wrong.

The entire exercise is to help you in best possible way.

Answer to question 1.
No, I have 12 separate monthly formulas, one for each month of the year. If I have today's date in the "dynamic" cell, then all of the monthly formulas will show the totals for each of their respective months. Whatever month is current, August for now, it can only show me the total for August up to today.
But if I enter 6/17/22 into the dynamic cell, the 12 formulas will still show me totals for Jan, Feb, Mar, Apr, and May but only for the month of June up to the 17th.
The other months formulas should NOT show me any totals for any months after 6/17/22.
Ex. Jul and Aug and obviously the rest of the year will all be zero.

 

[JohnZ1156]

How about

=SUMIFS(Table4[Flux 2],Table4[Date],">="&$A47,Table4[Date],"<="&MIN(EOMONTH($A47,0),$A$2))

where A2 holds the limiting date.

Thank you Fluff !!!
That did the trick.
Now, I just have to break it apart so I can understand what you did.
Thank you again !!! John

 

[SanjayGMusafir]

Thank you Fluff !!!
That did the trick.
Now, I just have to break it apart so I can understand what you did.
Thank you again !!! John

The trick lies in -

MIN(EOMONTH($A47,0),$A$2))

where formula asks system to choose between End of Month or Cell A2 which has a lower value.