Sumproduct and Subtotals

[Joe Sorrenti]

My sumproduct formula below is working fine.

=ROUND(SUMPRODUCT((B:B ActiveEmployeeArea <> "1")*(D
ActiveEmployeeArea)),0)

However, I'd like to expand it to subtotal visible rows in the range when it
is filtered. I need the Subtotal(9 function somewhere, but it does not
seem to work. Any help would be appreciated.

The subtotal below function is working, but I need to combine it with the
sumproduct function above.
=ROUND(SUBTOTAL(9,D ActiveEmployeeArea),0)

Any help would be appreciated.

 

[Frank Kabel]

Hi
try
ROUND(SUMPRODUCT(--(B1:B100<>"1"),SUTOTAL(3,OFFSET($A$1,ROW($A$1:$A$100
)-1,0)),D1100),0)

Note: I doubt your posted SUMPRODUCT formulas works as posted
-> ranges like B:B are not alloewd within SUMPRODUCT
-> incorrect range references

 

[Joe Sorrenti]

Frank,

Thanks for your solution. It worked and was exactly what I needed!!!

You were correct in that my original formula did not work as posted. I
edited it for simplicity.

Joe