Running Total of Days with Multiple Criteria

I'm at my wits end! I know that there is a somewhat simple formula to do what I'm trying to do, but for the life of me cannot figure it out!

I have a request sheet that students fill out through a form. They have a limit of 2 Discretionary Days, 2 Illness Days and 15 Flex Time Days. Students don't usually take all their allotted days in one go, so I need a way to track how many days they have taken previously so that the approver knows if the individual student has hit their limit.

I am able to calculate the number of days for each request with the formula =NETWORKDAYS([First Date of Absence]@row, [Last Date of Anticipated Absence]@row). I get stuck trying to calculate the running total based upon the email of the student and the type of absence (Discretionary, Illness or Flex Time).

Below are my unsuccessful attempts:

=SUMIFS([Total Number of Days]:[Total Number of Days], [Email Address]:[Email Address], [Email Address]@row, [OMS III & IV Absence Request]:[OMS III & IV Absence Request], "Discretionary Days", [OMS 3&4 Approval]:[OMS 3&4 Approval], "Approved")

=IF([OMS III & IV Absence Request]@row = "Discretionary Days", IF([OMS 3&4 Approval]@row = "Approved", SUMIF([Email Address]:[Email Address], [Noorda-COM Email Address]@row, [Total Number of Days]:[Total Number of Days}))

=SUM(Index({Max Days}, match([OMS III & IV Absence Request]@row, {Type of Absence}), 1)-Index([Total Number of Days]:[Total Number of Days, Match([Email Address]@row, [Email Address]:[Email Address]), 1)))

=INDEX(collect([Total Number of Days]:[Total Number of Days], [Email Address]:[Email Address],[Email Address]@row, [OMS III & IV Absence Request]:[OMS III & IV Absence Request], "Discretionary Days")), 1, SUM(INDEX(COLLECT({Max Days}, {Type of Absence}, [OMS III & IV Absence Request]@row), 1) - [Total Number of Days]@row)))

PLEASE HELP!!

Find more posts tagged with

Accepted answers

jmyzk_cloudsmart_jp

@Rebecca Ortinez

Isn't this something like that you are looking for?

Running Total of Days with Multiple Criteria.png

https://app.smartsheet.com/b/publish?EQBCT=06dfc61a360941aa9e75e1b15f5e98ba

[Discretionary Days Left] formula:

=IF([OMS III & IV Absence Request]@row = "Discretionary Days", [Discretionary Days Limit]# - SUMIFS([Total Number Anticipated Absence of Days]:[Total Number Anticipated Absence of Days], Email:Email, Email@row, [OMS III & IV Absence Request]:[OMS III & IV Absence Request], "Discretionary Days", [Row ID]:[Row ID], <=[Row ID]@row, [Year Group]:[Year Group], [Year Group]@row), "")

For Illness and Flex Time, change the "Discretionary Days" and [Discretionary Days Limit]# to correspond to those.

With the idea that the leave limit updates annually, the formula uses the "Year Group" to determine how many leave days remain in the same period, grouping them by 365 days from the base date.

=INT(([First Date of Absence]@row - [Base Date]#) / 365)

In the above-published demo sheet, you can edit the types of leave. So, check out how the formula works if you are interested.😁

You can use the filter to show only a.student or b.student.

All comments

Eric Law

@Rebecca Ortinez Have you thought about using a helper sheet? That way you aren't working formulas into your application sheet and it will be easier to see everything since you can sort by alpha. Also, I don't see you using the column formula feature, which you should since you need to apply the formula to all cells in the column. Just right click a formula in the column and select the bottom option of convert to column formula.

Rebecca Ortinez

@Eric Law thank you for the feedback! I have thought of adding a helper sheet--the problem is that it's not sustainable beyond a year. I try to make these sheets as self-sustaining as possible because once I figure out the formulas, I hand it off to the sheet owner (who are not necessarily Smartsheet savvy).

I did not enable the column formulas because I find it annoying to have to right-click and choose "Edit column formula" instead of just double-clicking the cell to edit.

Eric Law

@Rebecca Ortinez Why isn't sustainable beyond a year? To make it so you have 1 form for everything.

Your Helper sheet should also have a row ID Column that you can number 1-100 and you can use the following formula for the Email Address =IFERROR(INDEX(DISTINCT({Email Address Range 1}), [Row ID]@row, 1), "") where the Email Address Range 1 is the whole column of the intake sheet.

For your Intake sheet, I would create an archive that runs once a year that makes it so the date last date of absence is in the past. The only issue you will have, which you will need to solve regardless is when people's absence days straddle the new year.

Then an archive sheet that takes all the intake rows that you have to archive. I would also set a purge date so you don't run out of room on that sheet.

jmyzk_cloudsmart_jp

@Rebecca Ortinez

Isn't this something like that you are looking for?

https://app.smartsheet.com/b/publish?EQBCT=06dfc61a360941aa9e75e1b15f5e98ba

[Discretionary Days Left] formula:

For Illness and Flex Time, change the "Discretionary Days" and [Discretionary Days Limit]# to correspond to those.

With the idea that the leave limit updates annually, the formula uses the "Year Group" to determine how many leave days remain in the same period, grouping them by 365 days from the base date.

=INT(([First Date of Absence]@row - [Base Date]#) / 365)

In the above-published demo sheet, you can edit the types of leave. So, check out how the formula works if you are interested.😁

You can use the filter to show only a.student or b.student.

Rebecca Ortinez

Thank you for all of your help! I actually created a helper sheet that reads from the original sheet that houses all the submissions by students through a form. I then added all the student emails on the helper sheet and track both allotted days and days taken. Suprisingly, the formulas were pretty simple (see screenshots).

Rebecca Ortinez

The screenshot cut off my formula. Here it is in its entirety: =SUMIFS({Days Out}, {Email}, Email@row, {Absence Type}, "Discretionary Days")

So it's totaling up the days out on the original sheet by matching the email address of the student and also matching the absence category (discretionary).

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.