# COUNFIFS and FIND formula for weekly schedule

edited 06/18/20

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

Tags:

• ✭✭✭✭✭✭

When you say "not working", are you getting an inaccurate count or are you getting an error?

• Hi Paul, I am getting # INCORRECT ARGUMENT SET

One solution is surely to replicate the formula for each day (example below) and it works, just wondering if there may be a better solution that doesn't require all that effort in adjusting the syntax. Taking into account that these formulas will be replicated for many different variables.

=COUNTIFS(Monday:Monday, 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)) +=COUNTIFS(Tuesday:Tuesday, 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))+=COUNTIFS(Wednesday:Wednesday, 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)).......and so on

• ✭✭✭✭✭✭

That error is coming from repeating your [Dates Formula] range.

[Dates formula]:[Dates formula], >TODAY(),[Dates formula]:[Dates formula], [Dates formula]:[Dates formula], <TODAY(30)

Try removing one of the ranges from the bold portion so that your syntax sticks with

range, criteria, range, criteria

range, criteria, range, range, criteria

• ✭✭✭✭✭✭

You are also going to want to remove the = from the start of each COUNTIFS except for the first one.

The = sign simply tells SS that you are going to be writing a formula into the cell, so you only need the one at the beginning.

Example:

You have:

=COUNTIFS() + =COUNTIFS() + =COUNTIFS

whereas it should be

=COUNTIFS() + COUNTIFS() + COUNTIFS()

• edited 06/18/20

Sorry Paul,

the repetition and the "=" were typos, I don't actually have that repetition in my formula in Smartsheet. Apologies

The long formula in my last message is correctly working and prompts no error, it's just very long to be adapted in multiple formula scenarios.Takes a lot of manual work. Just this.

Do you think it may be possible to set the Monday:Friday range at the beginning of the formula somehow?

• I see now. Perfect. There is no easy escape from some good old manual work in this case I guess.

Many thanks for your time and your kind support Paul. As always, much appreciated.

Have a great day!

• ✭✭✭✭✭✭

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!