Excel: COUNTIF for Filtered Data

Question

I'm trying do a "COUNTIF" on a list of 24 items (a list of times, in number of hours).

For some background, the formulas :

=COUNTIF(H2:H25,7)

=COUNTIF(H2:H25,"<7")

return to me a list of 19 "full-day" (7-hour) incidents, and 5 "part-day" (less than 7-hour) incidents, respectively.

The formula:

=SUBTOTAL(102,OFFSET(H$2:H$25,ROW(H$2:H$25)-ROW(H$2),0))

returns to me the total number of filtered incidents.

I'm having difficulty getting the formulas to extracted the filtered number of full-day incidents.

=SUMPRODUCT(--(H$2:H$25=7),SUBTOTAL(2,OFFSET(H$2:H$25,ROW(H$2:H$25)-ROW(H$2),0)))

does not work. How do I correct this formula?

Thanks!

Solution

The first argument of OFFSET has to be a single cell like this

=SUMPRODUCT(--(H$2:H$25=7),SUBTOTAL(2,OFFSET(H$2,ROW(H$2:H$25)-ROW(H$2),0)))

or you can use the whole range (sometimes useful if you have a named range) but you'll need to specify the height and width arguments of OFFSET [both as 1] like this

=SUMPRODUCT(--(H$2:H$25=7),SUBTOTAL(2,OFFSET(H$2:H$25,ROW(H$2:H$25)-ROW(H$2),0,1, 1)))