0

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!

0

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)))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.