COUNTIFS using MultiSelect as criterion

Emilio Wright

I have a sheet that is tracking all of the tasks users need to do in a year. They enter the recurrence of the task as Daily, Weekly, and Monthly. They also enter the Start Date and End Date (ex. 3/15/2023). I then create rows for each month the task is running. So 3/1/2023 - 5/31/2023 would have three rows (March, April, and May).

The issue comes up for the Weekly recurrence. If users select Weekly, they will be asked to pick what days of the week they complete the task, which is a multi-select picklist. If a users selects only one day (ex. Monday), the formula works fine. If the users selects more than one day (ex. Monday Tuesday), the formula gives a 0.

I have a formula on a column called "Project Allocation Days" that counts the number of times that the particular day name (ex. Monday) appears in the given month and year based on a separate sheet that is a basic date table, similar to those used in SQL, to track the number of hours per month that the user spends on this task.

The formula:

=COUNTIFS({day_of_week_name}, HAS(@cell, [Recurrence Day Names (weekly)]@row), {month_name}, HAS(@cell, [Month_]@row))

The Date Table:

Example of one that works and one that doesn't:

Can you help me figure out what I am doing wrong with the formula? I have tried using Contains, but still doesn't work.

jmyzk_cloudsmart_jp

@Emilio Wright

In your formula,

=COUNTIFS({day_of_week_name}, HAS(@cell, [Recurrence Day Names (weekly)]@row)... ,

if you select Monday and Tuesday as [Recurrence Day Names (weekly)]@row multiple drop-down, the HAS function's criterion is "MondayCHR(10)Tuesday".

Please note CHR(10) divides each selection of a multiple-drop-down cell.

So, if, for example, in the range {day_of_week_name}'s @cell is Monday, the HAS function is evaluating if "Monday" has "MondayCHR(10)Tuesday".

That is why you get zero when you select multiple days of the week.

You could write a formula like,

=IF(HAS( [Recurrence Day Names (weekly)]@row, "Monday"), COUNTIFS({day_of_week_name}, HAS(@cell, "Monday")..., 

+IF(HAS( [Recurrence Day Names (weekly)]@row, "Tuesday"), COUNTIFS({day_of_week_name}, HAS(@cell, "Tuesday")..., .....

But such a formula gets too long to be manageable. 

URL to this dashboard is at the bottom.

So, I would instead add a column in the "Date Table" that list the IDs of the sheet with the "Recurrence Day Names" column by COLLECT function, and count the number of the IDs with the criterion like HAS(@cell, [Recurrence Day Names]@row).

So, the formula to list IDs is like

=JOIN(COLLECT({ID}, {Month}, [month_number]@row, {Day of Week Selection}, HAS(@cell, [Recurrence Day Names]@row)), ",")

For example, if IDs 1 and 3 select WED, the 03/01/23 Wed row will list 1 and 3 in the IDs column.

example

ID Selection

1 Mon, Wed

2    Mon

3    Wed

Date      Day of Week Name IDs

03/01/23  Wed 1, 3

Then, you can count the number of IDs that has days of the week selected at multiple-drop-down with a formula as follows;

=COUNTIF({IDs}, CONTAINS([ID Text]@row, @cell))

I need to convert IDs (number) to text to use them in the CONTAINS function with a formula, [ID Text] @ row = JOIN(ID @ row), for example.

Please take a look at a demo dashboard at the following URL that explains the above methods.

https://app.smartsheet.com/b/publish?EQBCT=88fc6af8035e430d9e665c707b88af89

jmyzk_cloudsmart_jp

@Emilio Wright

In your formula,

=COUNTIFS({day_of_week_name}, HAS(@cell, [Recurrence Day Names (weekly)]@row)... ,

if you select Monday and Tuesday as [Recurrence Day Names (weekly)]@row multiple drop-down, the HAS function's criterion is "MondayCHR(10)Tuesday".

Please note CHR(10) divides each selection of a multiple-drop-down cell.

So, if, for example, in the range {day_of_week_name}'s @cell is Monday, the HAS function is evaluating if "Monday" has "MondayCHR(10)Tuesday".

That is why you get zero when you select multiple days of the week.

You could write a formula like,

=IF(HAS( [Recurrence Day Names (weekly)]@row, "Monday"), COUNTIFS({day_of_week_name}, HAS(@cell, "Monday")..., 

+IF(HAS( [Recurrence Day Names (weekly)]@row, "Tuesday"), COUNTIFS({day_of_week_name}, HAS(@cell, "Tuesday")..., .....

But such a formula gets too long to be manageable. 

URL to this dashboard is at the bottom.

So, I would instead add a column in the "Date Table" that list the IDs of the sheet with the "Recurrence Day Names" column by COLLECT function, and count the number of the IDs with the criterion like HAS(@cell, [Recurrence Day Names]@row).

So, the formula to list IDs is like

=JOIN(COLLECT({ID}, {Month}, [month_number]@row, {Day of Week Selection}, HAS(@cell, [Recurrence Day Names]@row)), ",")

For example, if IDs 1 and 3 select WED, the 03/01/23 Wed row will list 1 and 3 in the IDs column.

example

ID Selection

1 Mon, Wed

2    Mon

3    Wed

Date      Day of Week Name IDs

03/01/23  Wed 1, 3

Then, you can count the number of IDs that has days of the week selected at multiple-drop-down with a formula as follows;

=COUNTIF({IDs}, CONTAINS([ID Text]@row, @cell))

I need to convert IDs (number) to text to use them in the CONTAINS function with a formula, [ID Text] @ row = JOIN(ID @ row), for example.

Please take a look at a demo dashboard at the following URL that explains the above methods.

https://app.smartsheet.com/b/publish?EQBCT=88fc6af8035e430d9e665c707b88af89

Emilio Wright

@jmyzk_cloudsmart_jp Thank you so much for the in-depth explanation. I was able to use all the information you provided to decide what I will end up doing. I had already figured that I could do sums of IF statements, but as you suggested, it would be a big-ish formula. I never wrote it then, but decided to write it once you suggested it, thinking I must not be too crazy. I also went ahead and used the other method as well. Here is my verdict, taking into consideration that the sheet where I keep all the tasks is about 6k rows deep and is already slow as it is.

Using the ID method does not give me the count immediately, obviously. I have to save the Task sheet so that the Date Table sheet can update its ID column and then refresh the Task sheet which ends up taking about a minute. I'm not up for that.

See the issue in the screenshot here. It doesn't update unless I save and refresh:

Using the sum of IFs gives me immediate results. Besides, it's just 5 IFs:

By the way, I am not sure why I was able to get away with not using the Join for the ID Text method like you mentioned. But you can also just nest those formulas if needed like this "=COUNTIF({IDs}, CONTAINS(JOIN(ID@row), @cell))". But like I said, I was able to just leave it as "=COUNTIF({IDs}, CONTAINS(ID@row, @cell))".

Here is just a screenshot showing the other method you mentioned:

Again, I truly appreciate you taking the time to tackle this ask, it really hurt me not being able to understand why it wasn't working to begin with. I never knew about CHR(10).

EDIT: I ended up using a SUM() instead of +'s

SUM(IF(HAS([Recurrence Day Names (weekly)]@row, "Monday"), COUNTIFS({day_of_week_name}, HAS(@cell, "Monday"), {month_name}, HAS(@cell, [Month_]@row)), 0),IF(HAS([Recurrence Day Names (weekly)]@row, "Tuesday"), COUNTIFS({day_of_week_name}, HAS(@cell, "Tuesday"), {month_name}, HAS(@cell, [Month_]@row)), 0),IF(HAS([Recurrence Day Names (weekly)]@row, "Wednesday"), COUNTIFS({day_of_week_name}, HAS(@cell, "Wednesday"), {month_name}, HAS(@cell, [Month_]@row)), 0),IF(HAS([Recurrence Day Names (weekly)]@row, "Thursday"), COUNTIFS({day_of_week_name}, HAS(@cell, "Thursday"), {month_name}, HAS(@cell, [Month_]@row)), 0),IF(HAS([Recurrence Day Names (weekly)]@row, "Friday"), COUNTIFS({day_of_week_name}, HAS(@cell, "Friday"), {month_name}, HAS(@cell, [Month_]@row)), 0))