SUMPRODUCT including empty cells :(

Not too experienced with advanced Excel, mainly cobbling information from the web - so please be gentle

I have found a formula to report a count on a filtered range of cells on another worksheet (this is turned into a % by /by L10).

This works great for reporting 0, +1, +2....+4 etc. However I need it to then report anything +5 or above.

That is where things fall down as it reports 427%. I presume it is counting the empty cells


=SUMPRODUCT(SUBTOTAL(3,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

How can I edit so it does not include empty cells

Any ideas?

Thank you in advance

Fanstastic - works a treat and also solves a further issue for me in the process

Thank you

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)

Thanks for your help so far....I have now moved on to creating a progress matrices and am learning a lot along the way.

I have tried to adapt your formula so that in this different report it will count when a one set of cells is between 13 and 9 AND when another set of cells is less than 15. However it doesn't work...where am I going wrong? I tried to put a , to differentiation between the first criteria and the second, but Excel corrects this to a *

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

Sorry - school boy error

I think I have been looking it so long I have become ))(( blind

I owe you a pint!

Thanks