July 23, 2017
Hi Mynda,
So, I need to sum the values R2:R32 until and unless 15th August appears. Once 15th August appears as current date, then sum R33:R63 unless 15th September appears, so on and so forth. Now this might give some irregularities in the month of Feb/March as the number of days are like 28/29 as per the leap year. Therefore, needed the formula to be considered from the fixed date's (due date) perspective, rather than number of days i.e. (30 days).
Appreciate your time and attention. Thanks for the prompt response. 🙂
July 16, 2010
Hi Jay,
So, if your due date in cell A2 is 15th August 2017 then you want to sum the "in progress" values in column R that have a date less than 15th August 2017? If so, you can use this formula:
=SUMIF($Q$2:$Q$35,"<"&A2,$R$2:$R$35)
Changing the date in cell A2 will recalculate the formula accordingly.
Learn SUMIF here: https://www.myonlinetraininghu.....-explained
Mynda
July 23, 2017
July 16, 2010
Hi Jay,
So now you know your explanation needs to be this clear, please rephrase your second question and give context and examples. e.g. I don't know what happens when the date today is 2nd August 2017. Should the date range be 15th July 2017 to 15th August 2017, or should it now be 15th August 2017 to 15th September 2017, or something else. What does "as per the current month" mean?
For your original question, try this formula, where A2 contains the start date for your range and A3 contains the end date:
=SUMIFS($R$2:$R$35,$Q$2:$Q$35,">="&A2,$Q$2:$Q$35,"<="&A3)
Also, are you sure you want to include the 15th in July and August, or should it be from the 16th July to the 15th August?
Mynda
July 16, 2010
Hi Jay,
Thank you for clarifying. In future please take the time up front to make your questions this clear. It will save everyone time.
In the attached file I have used the SUMIFS function. It's not a complicated function and it will serve you well to take the time to learn it here:
https://www.myonlinetraininghu.....-explained
I've also used the TODAY function to automatically calculate your 'Next Due Date'. I assume the In Progress values won't exceed the 'End Date' so this isn't really an issue, but I've factored in the End Date anyway.
Mynda
July 23, 2017
Hi Mynda,
Moving ahead, I've arrived at two more complex situations, based on:
1) merging two different IF formulas in one cell, which should satisfy both the formulas' requirements.
2) changing the values of a cell which contains IF formula, based on time duration which is validated from a list of time frame.
Please find the attached file for your reference. Kindly revert for any clarification.
Jay
1 Guest(s)