Times Available Formula

Kris Peeters

Instead of using a doodle poll, I'm using a smart sheet form. What would be the easiest way to tally the rows and show what times each of the people have in common?

I'm thinking of a COUNT formula but I'm not quite sure how to set that up. I've attached a screenshot.

Here are the time slots for each day:

08:00 - 09:00 am

08:30 - 09:30 am

09:00 - 10:00 am

09:30 - 10:30 am

10:00 - 11:00 am

10:30 - 11:30 am

11:00 - Noon

11:30 - 12:30 pm

12:00 - 01:00 pm

12:30 - 01:00 pm

01:00 - 02:00 pm

01:30 - 02:30 pm

02:00 - 03:00 pm

02:30 - 3:30 pm

03:00 - 04:00 pm

03:30 - 04:30 pm

04:00 - 05:00 pm

Thank you!!!

times available.JPG

zealvert

Dear @Kris Peeters

You can create a helper column (with its name like "Common Slots Count") and insert the following function in it:

=COUNTIF([Slot time]:[Slot time], Slot time@row)

Next, right-click the helper column, and convert it to a column formula as shown below:

With the above function, the helper column (Common Slots Count) will start showing the slot times the current row has in common.

Finally, you can create a filter in the sheet that shows only those rows whose value in the helper column meets the given criteria.

Further, you can sort the helper column as Descending to show the rows with the most common slots --at the top of the sheet.

Hope this helps!