COUNFIFS and FIND formula for weekly schedule

Alessandro Terranova

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

Paul Newcome

No. The problem is that all ranges within the same function must be the same size. Since [Dates Formula]:[Date Formula] is a single column, then all other ranges within the COUNTIFS must also be a single column.

The only option to be able to include Monday:Friday into your range would be to add some date type columns and replicate the data in [Dates Formula] until you have the same number of dates columns as you do Weekday Columns.

THEN you could use something along the lines of

Monday:Friday and [1st Date Column]:[5th Date Column]

But then you would also need to do the same thing with the Assigned column and expand it out to 5 columns wide.

Paul Newcome

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

Alessandro Terranova

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

Paul Newcome

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

instead of

range, criteria, range, range, criteria

Paul Newcome

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()

Alessandro Terranova

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?

Paul Newcome

No. The problem is that all ranges within the same function must be the same size. Since [Dates Formula]:[Date Formula] is a single column, then all other ranges within the COUNTIFS must also be a single column.

The only option to be able to include Monday:Friday into your range would be to add some date type columns and replicate the data in [Dates Formula] until you have the same number of dates columns as you do Weekday Columns.

THEN you could use something along the lines of

Monday:Friday and [1st Date Column]:[5th Date Column]

But then you would also need to do the same thing with the Assigned column and expand it out to 5 columns wide.

Alessandro Terranova

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!

Paul Newcome

Happy to help! 👍️