Google Sheets - Finding what occurs the most by using data from 3 columns

Question

I've been pulling my hair out trying to work out the best way to solve this one.

I have a spreadsheet that includes 3 columns.

• Column A is a date (Monthly breakdown)
• Column B includes time (hourly breakdown)
• Column C has a count of events that occurred within that hour on the specific date

What I'm trying to do is find the most common time that an event occurred. So I don't really need the date, I'm just trying to work out what hour of the day the event is most likely to occur, so that I can order from best hour to worst.

So I know I need to somehow combine the hours that match against various dates, along with their event totals, but I can't work out how to do it.

Answer 1

The best way is to create a pivot, but if you want to do it with a formula, use SUMIF:

=SUMIF($B$2:$B$12;A16;$C$2:$C$12)

Answer 2

You mentioned:

...what hour of the day the event is most likely to occur, so that I can order from best hour to worst.

You could also use a single query formula

=QUERY(B1:C, 
         "select B, sum(C) where B is not null group by B order by sum(C) desc label sum(C) 'Best ↓' ",1)

Functions used:

• QUERY