With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Is there a formula that would sum only the visible columns or rows in an excel spreadsheet. I have the numbers 4 in A1 2 in B1 3 in C1 and 1 in D1 I hide columns A and D the answer in E1 should be 5. If I unhide column A the answer in E1 would change to 9. I realize the formula would be somewhat the same for rows.

Would it be too much trouble to use an extra row?

1. Enter numbers in A1:E1

2. Enter this formula in A2: =CELL("width",A1)

3. Copy the formula from A2 all the way to E2.

4. Use this formula in F1:

=SUM(IF(A2:E2>0,A1:E1,0))

It's an array formula so you must press Ctrl+Shift+Enter afterwards.

Done.

Unfortunately, the formula isn't recalculated automatically when you hide/unhide columns. It will recalculate when you type anything to the sheet or if you press F9 (the "calculate now!" button)

Ture Magnusson

Karlstad, Sweden

Application.Volatile

right after the lines

Public Function SumVisible(a As Range) As Double

Dim c As Range

Dim value As Double

that should take care of the problem. This causes it to recaculate automatically.

blakeh1 that works if I press F9 but I'm still hopeing it will recalculate automatically.

Thanks so far tho

As for VBouhkAr's macro, I would pass the summands/sumrange as ranges to the function and not as string, otherwise inserting a row/colum 'above them" will foul up the function call.

Ps good to hear from you agin CRI

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

You can use user-defined function.

Press Alt-F11 (open VBA editor), add module and copy-paste next code:

Public Function SumVisible(a As Range) As Double

Dim c As Range

Dim value As Double

value = 0

For Each c In a

If c.EntireColumn.Hidden = False And c.EntireRow.Hidden = False Then

If IsNumeric(c.value) = True Then

value = value + c.value

End If

End If

Next

SumVisible = value

End Function

after that on your sheet you can use this function:

=SumVisible("A1:D1")

=SumVisible("B1:B20")

Hope it hepls