3

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?

1
  • What do you mean by "visible"? Can you post up a screen shot. In the words of Tom Cruise, "Help me... Help you...".
    – ejbytes
    Aug 6, 2016 at 1:58

3 Answers 3

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)))
6
  • Have you tested it? It doesn't seem to work. Aug 6, 2016 at 8:19
  • yes I have... just keep in mind that subtotal does not work with manually hidden rows. you need to filter the range Aug 6, 2016 at 8:21
  • @Dirk Reichel Are you sure about that? Do you understand the difference between, for example, =SUBTOTAL(9, and =SUBTOTAL(109,?
    – XOR LX
    Aug 8, 2016 at 7:58
  • @XORLX yes I am sure. If you run the the formula via evaluate formula you will see that column A is multiplied by column B while the filtered rows of column B will return as 0. This way only the products of the visible lines will be summed up. Still there is the possibility that I am wrong, but right now, I am sure that is exactly what the OP is looking for... Aug 8, 2016 at 8:05
  • 1
    Yes, but that is not the same as saying that "subtotal does not work with manually hidden rows". It does, provided you apply the appropriate parameter.
    – XOR LX
    Aug 8, 2016 at 8:06
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.

2
  • 1
    Is there a specific reason you are repeating already accepted answer? I'd recommend deleting this. I'd also recommend that you check the Help section answering and asking - good answers and questions are how you collect reputation. That will also help you understand why this isn't considered a good answer, and why the community may well decide to delete it if you don't. Sep 29, 2021 at 23:55
  • The formulas are different! One uses 109, the other 9 in the subtotal. And the user explained the difference.
    – ZygD
    Sep 30, 2021 at 3:58
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)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .