4

Pretty straightforward.

I want to use a SUMPRODUCT but only for visible rows.

I've tried =SUMPRODUCT(A2:A10, SUBTOTAL(9,B2:B10)) ... didn't work, even as an array formula.

I'm probably missing something obvious but ... why doesn't this work? How can I get this to work?

feetwet
  • 1,857
  • 6
  • 23
  • 38
user45867
  • 165

3 Answers3

3

for doing that, use the formula:

=SUMPRODUCT(A2:A10,SUBTOTAL(9,OFFSET(B2:B10,ROW(B2:B10)-MIN(ROW(B2:B10)),0,1)))

enter image description here

As said in comments: keep in mind that SUBTOTAL does not work with manually hidden rows. Only rows which are hidden due to a "filter" will be skipped in the calculation.

EDIT
Thanks to XOR LX: If increasing the parameter of SUBTOTAL by 100 it will also work with manually hidden rows. The formula then would be:

=SUMPRODUCT(A2:A10,SUBTOTAL(109,OFFSET(B2:B10,ROW(B2:B10)-MIN(ROW(B2:B10)),0,1)))
Dirk Reichel
  • 447
1
=SUMPRODUCT(A2:A10,SUBTOTAL(109,OFFSET(B2:B10,ROW(B2:B10)-MIN(ROW(B2:B10)),0,1)))

It will work on both rows hidden manually or by filter.

ZygD
  • 2,577
Rauf Waheed
  • 21
0

Alternatively, you can add a new column that outputs 0 or 1 if the row is hidden and add this array to the SumProduct formula.

Formula for the IsHidden column:

= IF( SUBTOTAL(103,A2)=1, 1, 0)
Destroy666
  • 12,350
eroock
  • 1
  • 1