Thinking about this a little more, I presume the "empty" cells must actually have a text value in them otherwise SUBTOTAL wouldn't count them, you can probably get the right result without the extra condition, just by changing the 3 to a 2 in the SUBTOTAL function, that will then only include cells with a numeric value, i.e.
=SUMPRODUCT(SUBTOTAL(2,OFFSET('Y4 R'!$AT$16,ROW('Y4 R'!$AT$16:$AT$120)-ROW('Y4 R'!$AT$16),0)),('Y4 R'!$AT$16:$AT$120>=5)+0)/'Y4 R'!L10
Audere est facere
Great
Based on your correction I have tried to nudge the formula further ....I have tried to add <13 and >=9 and then >=24 and <27
But reports 0 when there is actually 1 incidence....
=SUMPRODUCT(SUBTOTAL(3,OFFSET('Y4 R'!$EA$16,ROW('Y4 R'!$P$16:$EA$120)-ROW('Y4 R'!$EA$16),0)),('Y4 R'!$EA$16:$EA$120<13)*('Y4 R'!$EA$16:$EA$120>=9)*('Y4 R'!$EC$16:$EC$120>=24))*('Y4 R'!$EC$16:$EC$120<27)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks