Forum Discussion

Phishdawg's avatar
Phishdawg
Brass Contributor
May 04, 2023
Solved

Number of Tickets Per Hour of Given Day

Thanks to the wisdom of @mathetes I have almost got my puzzle solved.

I now seek the number of 'Tickets' issued for each hour of the day represented in Column C. I currently have a formula that has a static date matching 'Selected Date' giving me the total 'Tickets' for the entire day. I need Column D to show how many for each hour.

Column C formula - =SORT(UNIQUE(FILTER(HOUR(DailyTransactionSht!D2:D782330),DailyTransactionSht!A2:A782330=HourlyAverageSht!C3)))

 

Column E formula - 
=IFERROR(AVERAGE(FILTER(DailyTransactionSht!$H$2:$H$782330,HOUR(DailyTransactionSht!$D$2:$D$782330)=HourlyAverageSht!C6)),"")

Column D formula - 
=COUNTIF(DailyTransactionSht!A2:A782330,"6/3/2019")

 

Formulas and Functions
  • mtarler's avatar
    mtarler
    May 04, 2023

    Phishdawg I think it will be:

     

    =ROWS(FILTER(A2:A782330,
      (HOUR(DailyTransactionSht!D2:D782330)=C4)*
      (DailyTransactionSht!A2:A782330=$C$1)))

    where I assume C1 is the location of that date and C4 is the location of the hour

     

3 Replies

Sort By
  • mtarler's avatar
    mtarler
    Silver Contributor
    May 04, 2023

    Phishdawg I think it will be:

     

    =ROWS(FILTER(A2:A782330,
      (HOUR(DailyTransactionSht!D2:D782330)=C4)*
      (DailyTransactionSht!A2:A782330=$C$1)))

    where I assume C1 is the location of that date and C4 is the location of the hour

     

    • Phishdawg's avatar
      Phishdawg
      Brass Contributor
      May 04, 2023

      mtarler 

      Actually had a few typos, which I've corrected.

      Formula works as advertised.

      Thank you!

       

      • mtarler's avatar
        mtarler
        Silver Contributor
        May 04, 2023
        I'm glad you got it to work. I'll pat myself on the back creating the formula without having access to the actual workbook and the only "typos" I see are C1 & C3 needed to be C3 & C6, which I totally called out in my comment 🙂
        Note, I noticed you added the sheet name to the filter range but you really shouldn't need that since you don't care what you filter you just want to know how many rows.
        BTW, the "better" way to do this might be
        =COUNTIFS(DailyTransactionSht!$D$2:$D$782330,TIME($C6,0,0),DailyTransactionSht!$A$2:$A$782330,$C$3)
        but I wasn't sure if column D was only a time value and hence this would work or if there might also be a date (even if that date value isn't shown) and hence this version wouldn't work. So again, without the actual sheet I opted for the more tolerant option.

Resources