Times Available Formula

Options

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!!!


Answers

  • zealvert
    zealvert ✭✭✭✭
    Options

    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!