How to create a formula that calculates a column called "teach days" for monthly data.

System

This discussion was created from comments split from: Count specific day qty in date range..

Heather White

@Genevieve P.

I'm looking to create a formula that calculates a column called "teach days" for monthly data. So for example from 9/1/24 to 9/30/24 i want to only grab the number of days in the "teach days" column. I want to do this for each month. How would i do this in combination with an existing formula?

=SUMIFS({ATC Instructor Schedule 2025 Teach Days}, {ATC Instructor Schedule 2025 Type}, "Teach", {ATC Instructor Schedule 2025 Assigned to}, Contacts@row) and then add the monthly teach days.

Any assistance is appreciated.

Heather

NicoLHC

@Heather White do you wan to use the sum formula in a dashboard or metric? Maybe you could also rty to use a report .

Heather White

i currently have it in a sheet that i take that number and put in an excel document because i cannot figure out how to create a chart with all the data i need to display.

For all the tasks i have listed from Jan to December, i only want to pull monthly data for 11 people, separately for types of work they are doing.

NicoLHC

@Heather White why you put into excel? Are you not trained ?

I have the feeling you could make it easier … for example with reports or an metric sheet

But here is a formula you can try …=SUMIFS({ATC Instructor Schedule 2025 Teach Days},
{ATC Instructor Schedule 2025 Type}, "Teach",
{ATC Instructor Schedule 2025 Assigned to}, Contacts@row,
{ATC Instructor Schedule 2025 Date}, >=DATE(2024, 9, 1),
{ATC Instructor Schedule 2025 Date}, <=DATE(2024, 9, 30))

Heather White

No, no training, all self teachings with smartsheets. When i called support, they were unable to tell me how to get the visual i needed in a chart through smart sheet, so ive managed to get the calculation in smart sheet, however i have to put into an excel so i can create a chart that i cannot get from smartsheets.

I tried the formula adding the dates as above, but it's not calculating correctly.

=SUMIFS({ATC Instructor Schedule 2025 Range 4}, {ATC Instructor Schedule 2025 Range 2}, Contacts@row, {ATC Instructor Schedule 2025 Range 5}, [2024 Total Days]@row, {2025 Instructor Schedule Range 1}, >=DATE(2025, 2, 1), {2025 Instructor Schedule Range 2}, <=DATE(2025, 2, 28))

NicoLHC

@Heather White can you share the sheet ? Its easier, faster to find the problem .

Heather White

what email to share to?

NicoLHC

nico.roepnack@lighthouseconsultings.com