Excel Experts- I need your help

[BNB]

Ok, so I'm pretty good with Excel, but I can't figure this shit out.

I need a value to change as I use the filter to get rid of some other data. I know the SUBTOTAL function does this. But my value is calculated using the SUMPRODUCT function. ( =SUMPRODUCT(S4:S26,T4:T26)/SUM(S4:S26) ) is the formula I'm using, and it gives me a value of 3:56, which is what I want.

But if, for example, I want to filter out row 10, I need the value to be recalculated and show the sumproduct of what is currently showing on the sheet.

Am I making sense?

So I guess what I'm asking is.... is there a way to combine SUBTOTAL and SUMPRODUCT, and how?

 

[Mitchapalooza]

did you try setting it to Wumbo?

 

[Crystallas]

remove row 10 from your arrays.

=SUMPRODUCT(S4:S9,S11:S26,T4:T9,T11:T26)/SUM(S4:S9,S11:S26)

or do a mod function as a rule for the whole spreadsheet, and define uncountable cells. You need to decide whether you want to use mod functions early, because if you start doing that with normal formulas throughout the sheet, you'll break all the formulas.

 

[Monster]

That's what I was thinking... Also, what's Excel?

 

[Don't Care]

http://blog.contextures.com/archive...n-friday-subtotal-and-sumproduct-with-filter/