Formula to get best poll time

RGarrels

I have created a poll with dates as column headers and several times as multi-drop down options. It created a fantastic web-form I was able to share easily with collaborators both inside and outside my organization.

My question is, is there a formula I could apply in the Sheet summary to count the best time for each date, and/or the best time and date on the sheet overall?

Thank you!

Paul Newcome

Are you able to provide some screenshots for context?

RGarrels

I am looking for a quick way I could use this set up to detirmine the best times to host a meeting.

Paul Newcome

Assuming that by "best times" you mean the date/time that has the most votes, my suggestion would be to create a second sheet. You would maintain the date columns, and in a text/number column you would list all time slots once.

Then you would use a COUNTIFS with cross sheet references to get the total count for each timeslot on each date. You will need to create a new cross sheet reference for each date column, but the syntax would look something like this:

=COUNTIFS({1st Date Column}, HAS(@cell, [Time Slot Column]@row))

From there we could get into a little more complexity if you want to allow conditional formatting to provide a sort of heat map based on the votes so that the most votes are dark green, and the least votes are dark red with yellows and oranges in between, but that definitely adds to the complexity and gets tedious when building out the various conditional formatting rules. This part is only really worth it if you are planning on doing this multiple times and will be using this setup as a sort of template. I can't say it would be worth it for a one-off though.

RGarrels

Thank you.

My intention was to build this into a template that our team could re-use.

I will give this a try.

Paul Newcome

Ok. Let me know when you get the base built up with the scores for each timeslot on each date, and then we can walk through setting up the "heatmap" one step at a time if you are interested.

RGarrels

Sounds great. Thank you!