Sumif Function does not sum when today's date is within the range

hugs184

I'm trying to sum data from another sheet based on date range. My formula that I have is not summing the information. Summing PTO Taken (Jan until today) and Planned Days (today until the end of the year). Employee provides: 1) Start Date 2) End Date of their PTO and 3) Number of Days off. If Today's Date is within the Start/End Date of PTO, both formulas below don't sum the "Number of Days off". What am I missing?

Leaves Taken: =SUMIFS({Number of Days}, {Name}, @cell = [Executive Name]@row, {Start Date}, @cell >= [Reconcile Notes]$24, {End Date}, @cell <= [Reconcile Notes]$22 + 1, {SLT PTO List of Requests Range 1}, "PTO/Annual Leave")

Planned Days: =SUMIFS({Number of Days}, {Name}, @cell = [Executive Name]@row, {Start Date}, @cell >= [Reconcile Notes]$22, {End Date}, @cell <= [Column9]$27, {Type of Leave}, "PTO/Annual Leave")

KPH

Looking just at Leave Taken to start with.

Is the Jan date entered in the [Reconcile Notes]$24 cell and today's date in [Reconcile Notes]$22? That looks like what you are doing.

Are the Reconcile Notes, Start Date and Date columns all date formatted columns? They need to be. You use > or < to evaluate a text column.

If that isn't the issue, can you try removing one range and criteria pair from the function and see if it works, if not, remove another, and another, until it works, then try adding them back in to see which one is the problem.

hugs184

Thanks KPH for the quick response.

Yes - the assumptions you are making is correct. [Reconcile Notes]$24 cell is Jan 1, 2024. [Reconcile Notes]$22 is Today's date. Reconcile Notes, Start Date, End Date are formatted to DATE columns with the "Restrict to dates only" selected.

The different cafeterias work if my Start & End Date does not include Today's date. i.e. if PTO dates are June 24-28, I will get "0". If PTO dates are July 1-5, it sums the "Number of Days" PTO are taken (5 days). Note - the "Number of Days" is an inputted number value, it is not a calculated number.

KPH

If

• Start date is June 24
• End date is June 28,
• [Reconcile Notes]$24 cell is Jan 1, 2024
• and[Reconcile Notes]$22 is Today's date - june 25 or 26

Then

=SUMIFS({Number of Days}, {Name}, @cell = [Executive Name]@row, {Start Date}, @cell >= [Reconcile Notes]$24, {End Date}, @cell <= [Reconcile Notes]$22 + 1, {SLT PTO List of Requests Range 1}, "PTO/Annual Leave")

Would be 0 as the end date is after today’s date. This part of the sumifs would be false:

{End Date}, @cell <= [Reconcile Notes]$22 + 1

June 28 is not on or before June 25 + 1 or June 26 + 1

Therefore that row will not be included in the sum.

What do you want the formula to do in that situation? Sum the number of days up to today? You can possibly add another sumifs to the one you have to add the sum of rows where the end date is after today and subtract the number of days between today and the end date.

hugs184

yes, that would actually be perfect. What would be the formula for that? Perhaps, I have those calculations in a hidden column that follows the details I'm calculating?

KPH

I would add a hidden column to your original sheet and do the calculation there and then sum the new (hidden) column. Your current formula will also not work for any periods that start before, but end after, Jan 1st. So you need to account for three situations (possibly 4 if you need to stop counting at the end of the year).

1. Start before Jan 1st and end after - count only from Jan 1st to end
2. Start after Jan 1st end before today - count start to end
3. Start after Jan 1st but end after today - could from start to today

You can do that with an IF function. Your IF function will have three parts, one for each of the calculations above.

1.

Start with an IF like this to get the count for item 1

=IF([Start Date]@row < DATE(2024, 1, 1), "formula to count days from Jan 1st", "TBD")

I don't know how you are calculating days between start and end, maybe NETWORKDAYS, like this:

NETWORKDAYS(DATE(2024, 1, 1), [End Date]@row)

If so, the IF becomes:

=IF([Start Date]@row < DATE(2024, 1, 1), NETWORKDAYS(DATE(2024, 1, 1), [End Date]@row), "TBD")

Check that works. You will see "TBD" for any rows that start after Jan 1st. We are only calculating for those in group 1 at the moment.

2.

Next, you can replace the "TBD" with another IF function. If the first if is false, the second will be evaluated, and will check if the End Date is before TODAY plus 1 (which we need for item 2).

=IF([End Date]@row < (TODAY(+1)), "formula to count days from start to end", "TBD")

Put that in place of the "TBD" you already had in the formula from step 1:

=IF([Start Date]@row < DATE(2024, 1, 1), NETWORKDAYS(DATE(2024, 1, 1), [End Date]@row), IF([End Date]@row < (TODAY(+1)), "formula to count days from start to end", "TBD"))

Your counting formula here will be something like this (counts the days between the start and end date as we don't need to worry about the end being after tomorrow or the start before Jan 1st):

NETWORKDAYS([Start Date]@row, [End Date]@row)

Add that to your formula and check it works:

=IF([Start Date]@row < DATE(2024, 1, 1), NETWORKDAYS(DATE(2024, 1, 1), [End Date]@row), IF([End Date]@row < (TODAY(+1)), NETWORKDAYS([Start Date]@row, [End Date]@row), "TBD"))

3.

Finally you need the count of days up to today for periods that end after today plus 1.

That formula will probably be

NETWORKDAYS([Start Date]@row, TODAY())

Add that in place of the final TBD

=IF([Start Date]@row < DATE(2024, 1, 1), NETWORKDAYS(DATE(2024, 1, 1), [End Date]@row), IF([End Date]@row < (TODAY(+1)), NETWORKDAYS([Start Date]@row, [End Date]@row), NETWORKDAYS([Start Date]@row, TODAY())))

Note

Note - This won't work for anyone that has a period starting before Jan 1st and ending after tomorrow. Let me know if that is a problem and we can adjust it. It just needs and AND to be included in the IF.

Planned Days

You can make a similar column for planned days using the end date being on or after tomorrow. It might be as simple as:

=IF([End Date]@row >= TODAY(+1), NETWORKDAYS(TODAY(+1), [End Date]@row))

You may need Dec 31st for the end or leave that out if it doesn't matter.

Conclusion

You'll need to check the calculations do what you want, but the format for the IF should be good. Here is a quick illustration.

Sum

Now when you SUM you can sum the new column and remove the criteria so you just have:

=SUMIFS({Number of Days Jan 1 to today}, {Name}, @cell = [Executive Name]@row, {SLT PTO List of Requests Range 1}, "PTO/Annual Leave")

and

=SUMIFS({Number of Days Tomorrow onwards}, {Name}, @cell = [Executive Name]@row, {SLT PTO List of Requests Range 1}, "PTO/Annual Leave")