Using aggregate function with sum-product to avoid hidden rows

[Noah101]

Good day experts,

I'm using a sumproduct formula =(sumproduct (Handle time * contacts)/(sum contacts)

How do I use aggregate with this to avoid hidden rows?

 

[barry houdini]

The simple way is to add a helper column, e.g. assuming contacts are in column A then in Z2 copied down

=SUBTOTAL(2,A2)

That will now only show a 1 on visible rows that contain a number in column A

Now change your formula to

=SUMPRODUCT(Handle time * contacts * (helper=1))/SUBTOTAL(9,contacts)

To do the same without any helper column you can use this formula

=SUMPRODUCT(Handle time, SUBTOTAL(9,OFFSET(contacts, ROW(contacts)-MIN(ROW(contacts)),0,1)))/SUBTOTAL(9,contacts)

Assumes that contacts is a column of data
​

 

[Noah101]

This is excellent thank very much!