Hi Jay,

I don't understand the conditions you require. Please give an example of what dates you would sum the In Progress figures for, if the due date is July 15.

Mynda

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. 🙂

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.myonlinetraininghub.com/excel-2007-sumif-and-sumifs-formulas-explained

Mynda

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

Hi Mynda,

Got a bit confused here. Trouble in placing the given formula.

Please find the attached file with description of what's required.

Appreciate your efforts. Thanks.

Jay

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.myonlinetraininghub.com/excel-2007-sumif-and-sumifs-formulas-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

Hi Mynda,

Please accept my sincere apologies as I took more than some time to understand the complications by myself. Finally, cleared all the clutter from my mind and framed the requirement.

The formula just worked like a magic. Thank you so much.

Jay

No problem, Jay. Glad we got there in the end and next time it should be quicker and easier 🙂

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

Hi Jay,

Please start a new thread. New question = new topic/thread.

Before you post it, please make your question clearer by showing the value/desired calculation in column K. You have words, but not a numeric example of the desired result.

Thanks,

Mynda