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!
Best Answer
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)))