COUNFIFS and FIND formula for weekly schedule

Hi Community, hope you are well!


Hope someone could help with the following formula syntax.


I am building a weekly schedule for the creative studio.

I would like to calculate how to count a specific entry such as "Email" across the schedule.I have columns for Week starting date, assigned, daily schedule broken down in half an hour slots AND five columns for each week day where (Monday / Tuesday / Wednesday / Thursday / Friday) under which activities such as "Email" can be assigned.


Formulas I have tested so far:

This is WORKING if I count "Email" Mon to Fri all across the sheet(all months /all weeks):

Total Count "Email": =COUNTIFS(Monday:Friday, FIND("Email", @cell) > 0)


This is WORKING if I count "Email" Monday only (any individual day) this month:

Total Count this month: =COUNTIFS(Monday:Monday, FIND("Email", @cell) > 0, [Dates formula]:[Dates formula], MONTH(@cell) = MONTH(TODAY()))


Similarly this is WORKING if I count "Email" Tuesday only (any individual day) this week:

=COUNTIFS(Tuesday:Tuesday, FIND("Email", @cell) > 0, [Dates formula]:[Dates formula], WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()))


This is WORKING if I count "Email" Tuesday only (any individual day) in the next 30days:

=COUNTIFS(Tuesday:Tuesday, FIND("Email", @cell) > 0, [Dates formula]:[Dates formula], >TODAY(), [Dates formula]:[Dates formula], <TODAY(30))


This is WORKING if I count "Email" Monday only (any individual day) for a specific assigned name:

=COUNTIFS(Monday:Monday, FIND("Email", @cell) > 0, Assigned:Assigned, FIND("person name", @cell) > 0)


What is NOT WORKING:

I would like for instance to calculate "Email" occurrences in the next 30 days, or this month, or this week but must include the entire Monday to Friday range.

e.g. =COUNTIFS(Monday:Friday, FIND("Email", @cell) > 0, [Dates formula]:[Dates formula], MONTH(@cell) = MONTH(TODAY())) this doesn't work.

Bu also, especially, to calculate "Email" occurrences for a specific "person name" in the next 30 days but must include the entire Monday to Friday range.

e.g =COUNTIFS(Monday:Friday, FIND("Email", @cell) > 0, Assigned:Assigned, FIND("person name", @cell) > 0,[Dates formula]:[Dates formula], >TODAY(),[Dates formula]:[Dates formula], [Dates formula]:[Dates formula], <TODAY(30))


Hope the above is clear enough. You kind help would be much appreciated!

Thanks

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!