Implementing HOUR with COUNTIFS

Question

Table sheet tabulates data from the Data tab.

https://docs.google.com/spreadsheets/d/1YUTnJ_2xM6115gUV1MCReMR93k-8LuVupJ1ObdbuI0U/edit?usp=sharing

The breakdown is based on the Data tab, Col B and Col E.

Currently I am using a helper column to determine the hour recorded in the Date column (Col A).

I would like to modify the COUNTIF formulae in the Data tab to eliminate the need for the helper column. My current formulae is of the following format:

=COUNTIFS(Data!$A$18:$A,">"&$A$7,Data!$C$18:$C,E$2,Data!$B$18:$B,$C3,Data!$E$18:$E,E$1)

I have tried using the HOUR function within the COUNTIF formula (refer E3 on Table tab) but receive an error: "Array arguments to COUNTIFS are of different size."

I've also tried to implement the concept from this question, however, I wasn't successful.

player0 player0 · Accepted Answer · 2020-02-11T10:07:24

use:

=ARRAYFORMULA(COUNTIFS(Data!$A$18:$A, ">"&$A$7, 
                       Data!$C$18:$C,  E$2, 
                  HOUR(Data!$B$18:$B), $C3, 
                       Data!$E$18:$E,  E$1))

tho if Data!B18 is not in true hour format use:

=ARRAYFORMULA(COUNTIFS(Data!$A$18:$A, ">"&$A$7, 
                       Data!$C$18:$C,  E$2, 
                       Data!$B$18:$B,  $C3, 
                       Data!$E$18:$E,  E$1))

Implementing HOUR with COUNTIFS

1 Answers