Using sumproduct with only visible cells?

[Pezz3r]

Hi,

I have a sumproduct formula something like this:
=SUMPRODUCT(O9:O37,$M9:$M37)/SUM($M9:$M37)

However I am using a filter and I want the value to change depending on which rows are selected.

For simple SUMs I've used SUBTOTAL, is there an equivalent for SUMPRODUCT?

 

[jasonb75]

Welcome to the board!

Try using a helper column with subtotal.

In N9 (or next avaliable column) enter =SUBTOTAL(109,M9) and fill that down no N37

Then in your sumproduct formula add the criteria to compare the subtotal to the actual, for example

=SUMPRODUCT(O9:O37,--(M9:M37=N9:N37),$M9:$M37)

 

[barry houdini]

You could avoid a helper column by using this formula

=SUMPRODUCT(O9:O37,SUBTOTAL(9,OFFSET($M9,ROW($M9:$M37)-ROW($M9),0)))/SUBTOTAL(9,$M9:$M37)

 

[Pezz3r]

Thanks Barry that's perfect.

Was trying to avoid helper columns etc. if only to show off when this report gets circul8d

 

[jasonb75]

Thanks Barry that's perfect.

Was trying to avoid helper columns etc. if only to show off when this report gets circul8d

Does the result change with the filter?

I would have thought that you would need

=SUMPRODUCT(O9:O37,SUBTOTAL(109,OFFSET($M9,ROW($M9:$M37)-ROW($M9),0)))/SUBTOTAL(109,$M9:$M37)

To ignore the hidden rows.

 

[Pezz3r]

Looks like either work.

If I'm honest with you Jason, and I may as well be, I've no idea why. In the excel help it says use 109 to hide visible cells.

 

[jasonb75]

It could be a difference in data setup, I tried barry's formula to see how it worked and found the result was unchanged regardless of the filter, it also returned a value when there were no rows visible, which, in theory should result in a #DIV/0! error.

 

[Pezz3r]

Are you definitely using a filter or manually hiding the rows though?

 

[jasonb75]

Are you definitely using a filter or manually hiding the rows though?

I was doing manual to start, and forgot to change the formula back to barry's version before trying it on filter. Sorry if I caused any confusion.