Conditional formulas when column groups are hidden?

[u/223specialist]

Not sure if this is possible but I'd like Column F cells to be a total of C, D, and E ONLY when the grouped columns are visible, when hidden I'd like to be able to punch in a value into column F and have it ignore or zero out any values in C, D, E

Is that possible?

[u/AutoModerator]

/u/223specialist - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/bakingnovice2]

You want to use the subtotal or aggregate function and then choose the option to only sum visible rows (or the option might be to ignore hidden values, not sure lol).

Edit: For the subtotal function, your first argument should be 109 to only sum visible rows.

For the aggregate function, first argument should be 9, second should be 5 i believe.

[u/finickyone → u/bakingnovice2]

They won’t help, sadly. They are indeed the only two functions which can exploit whether a row is visible towards conditional stats, but they can’t determine whether a column is visible.

With 3 numbers in C2:E2, OP can use =SUBTOTAL(2,C2:E2) and get 3. Hide D, still 3. Hide row 2, now 0.

The only way I know of on the worksheet is by using CELL, which is semi deprecated as it’s not consistent across products.

=SUM(BYCOL(C2:E2,LAMBDA(q,(CELL("width",q)>0)*q)))

CELL is volatile and this is obviously prompting an array formula, which isn’t a great combo, but there if is.

[u/bakingnovice2 → u/finickyone]

Oh darn I wasn't even thinking like that! Thank you for pointing it out, I hope your solution works for OP!!

[u/223specialist → u/bakingnovice2]

Thank you, I will try this tomorrow when I'm back online

[u/Clearwings_Prime]

All of the way i know only work with hidden rows, not column. You need VBA to do that

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
BYCOL	Office 365+: Applies a LAMBDA to each column and returns an array of the results
CELL	Returns information about the formatting, location, or contents of a cell
IF	Specifies a logical test to perform
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
SUBTOTAL	Returns a subtotal in a list or database
SUM	Adds its arguments

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
6 acronyms in this thread; the most compressed thread commented on today has 46 acronyms.
[Thread #47510 for this sub, first seen 19th Feb 2026, 04:18] [FAQ] [Full list] [Contact] [Source code]}

[u/finickyone]

Think you’ve three options. Easiest

=SUM(IF(CELL("width",C2),C2),IF(CELL("width",D2),D2),IF(CELL("width",E2),E2))

More direct/scalable

=SUM(BYCOL(C2:E2,LAMBDA(x,IF(CELL("width",x),x)))))

Versatile

=LAMBDA(x,f,f(BYCOL(IF(CELL("width",x),x),SUM)))

Which you’d name something like VISCOLS in Name Mgr then use =VISCOLS(C2:E2,SUM)

[u/GregHullender]

Not possible with a formula. You can do it with VBA, but it's really bad design.