1

I have a row and I want to sum only visible cells, i know if it's a column I can use subtotal (109,range), but this one doesn't seem to work for cells in one row. Anyone knows how to sum only visible cells in a row?

Please click here for picture

enter image description here

emma12345
  • 107
  • 4
  • 10

3 Answers3

2

You can check the width of the cell.

=IF(CELL("width",A1)=0,"hidden","open")

you can then sum your cells as need it using IF and CELL

=IF(CELL("width",A1)=0,0,A1)

more info below: Ignoring a hidden column in an excel sum formula

Community
  • 1
  • 1
causita
  • 1,607
  • 1
  • 20
  • 32
  • Using this method, OP would have to create another group of data, then sum that, right? Of would there be a way to throw this into an array formula? – BruceWayne May 19 '17 at 16:18
  • 1
    @BruceWayne no `CELL` will not work in arrays. It can only be used one cell at a time. so to use this would require a helper row. – Scott Craner May 19 '17 at 16:28
  • Thank you, I actually found someway to go around it but I tried your formula. It works but the problem is, it's good for one time sum but if I changed the width of the cell i need to manually refresh it. What I am trying to do is I have a vba to automatically hide the columns I don't want and I hope the sum can change when I change the columns that have been hidden. – emma12345 May 19 '17 at 18:25
  • @ScottCraner Actually we can use CELL with arrays, with a little coercion. – XOR LX May 19 '17 at 20:32
  • 1
    It should be pointed out to the OP that this solution is not guaranteed to consider hidden columns only. Any column with a width of less than 0.5 will also be considered as equal to 0 in this construction. – XOR LX May 19 '17 at 20:34
  • @XORLX Out of curiosity, how? – Scott Craner May 19 '17 at 20:37
  • @ScottCraner Posted as answer - thought might be more useful there than as a comment – XOR LX May 19 '17 at 20:45
2

If a VBA solution is okay, this will work:

Function sumVisible(rng As Range) As Double
Dim cel As Range
For Each cel In rng
    If cel.EntireColumn.Hidden = False Then
        sumVisible = sumVisible + cel.Value
    End If
Next cel
End Function

Pretty straightforward - just checks if a cell in your range has a hidden column, if it's visible, sum it.

=sumVisible(D2:M2) is how you'd use it.

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
2

You can do this using a single worksheet formula alone, provided that none of the columns which remain unhidden will have a width of less than or equal to 0.5 (which, in practice, would be so narrow as to be virtually hidden in any case).

Assuming a range of A1:E1

=SUMPRODUCT(0+(CELL("width",OFFSET(A1,,N(INDEX(COLUMN(A1:E1)-MIN(COLUMN(A1:E1)),,))))>0),A1:E1)

Unfortunately this formula will not update automatically as changes regarding hiding/unhiding columns within the range are made. As such, it will be necessary to 'recommit' it each time you make changes in that respect; one way to do this is via going into the formula as if to edit it and then recommitting by pressing ENTER.

Perhaps of interest is this post.

Regards

XOR LX
  • 7,632
  • 1
  • 16
  • 15