Exclude Hidden Columns from Calculation

[karlruff]

Hello,

Is there a way to exclude hidden columns from a calculation?

For example, say in C3 i have =A1+B1.
In cell A1 is 4
In cell B1 is 12

Is there a way to make excel show 4 in C3 when i hide column B? (without changing the formula in C3 to =A1, obviously)

Thx!
Ben

 

[Richard Schollar]

Hi Ben

I may be wrong here, but I think you would have to use VBA here to produce that result as pure Excel alternatives (such as SUBTOTAL function) won't exclude hidden column members, only hidden row members. Is that something you might be interested in, or were you looking for a pre Excel solution?

 

[karlruff]

I'm hoping for a pure excel solution, as my VBA knowledge is lacking. Many thx for the response, and let me know if theres an easy vba way to do this.
Thank you
Ben

 

[pgc01]

Hi Ben

I agree with Richard that a better solution involves vba. I would like, however, to point out that there is a "almost good" solution:

=A1*(CELL("width",A1)>0)+B1*(CELL("width",B1)>0)

It has 2 problems:

- CELL("width",) gives you the width of the column rounded to the unit. This means that if you have a column very thin (less than half a character), you get a width of 0 even if that is not true.

1st conclusion: If you hide/unhide the columns, it should be OK. If, instead, you are playing with the widths of the columns, making them wider or narrower, then you may get a wrong result if you make it very (very) thin.

- Hiding/unhiding/changing the width of a column does not trigger calculation.

2nd conclusion: After a change in the width of a column you must either trigger calculation manually (F9) or do something else that triggers calculation, like changing the value of a cell.

These 2 drawbacks make this solution not good for all cases. With a Vba solution you won't have the first one. The second one will also not be easy in vba.

Hope this helps
PGC

 

[Richard Schollar]

=A1*(CELL("width",A1)>0)+B1*(CELL("width",B1)>0)

That's pretty darn cool PGC!

 

[Stormseed]

=A1*(CELL("width",A1)>0)+B1*(CELL("width",B1)>0)

Awesome stuff ! thanks for the info, sir.

 

[itr674]

Just to add to this thread, I used this formula today to count "X" in range W8:DE100. Still has same issues as PGC01 states above, but works in for this particular application.

First, I put formula CELL("width",W1)>0 in range W1:DE1.

Second, I put SUMPRODUCT(--($W$1:$DE$1=TRUE)*--($W8:$DE8="X")) in range T8:T100.

Remember to hit F9 to recalculate...