Subtotal to count non-hidden columns

[REDPOOL]

I have a linear count from 1 to 160 (J3:FM3) and I hide columns manually over time depending on a certain criteria. However, I would like to count how many columns I have left.
I believe you need to use the subtotal function, but I do not understand how to use it.
Thanks in advance.

[Pete_UK]

Instead of using =SUM(J3:FM3), you would use:

=SUBTOTAL(109,J3:FM3)

instead, and then any hidden columns will not be counted in the sum. XL Help lists what the values for the first parameter (i.e. the 109) can be and what they mean.

Hope this helps.

Pete

[TMS]

@Pete: it doesn't appear to work with hidden columns. Rows, yes; Columns, no. But the OP said COUNT, not SUM, so it would be option 102.

Please Login or Register  to view this content.

Not OK

Please Login or Register  to view this content.

OK but not what is wanted

Regards, TMS

[Pete_UK]

I've only ever used it for rows, so it was a bit of a guess - I suppose I should have tried it first !!

As for the number, I did advise to look them up in XL Help, so the OP might have found that.

Pete

[TMS]

@Pete: I'm with you, only used it for rows ... but I did check it, and was sadly disappointed. When you start to type the function and enter the bracket, it comes up with a list of the options.

[icestationzbra]

http://www.excelforum.com/excel-gene...n-columns.html

https://office.microsoft.com/en-us/e...010062463.aspx

The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using a function_num of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. But, hiding a row in a subtotal of a vertical range does affect the subtotal.

[Richard Buttrey]

Hi,

One way would be to use a helper row, say J1:FM1 and in J1 copied across enter

Please Login or Register  to view this content.

Now your formula for the columns left would be

Please Login or Register  to view this content.

There's only one small problem with this approach and that's that the CELL function needs a sheet recalculation since it's non volatile when a column is toggled between visible and hidden. So when you hide a column you'll also need to F9 as well - or if your process includes a macro incorporate the Calculate in the macro

[REDPOOL]

Maybe I wasn't specific enough, or I'm lost.. I actually want to count the number of columns left, not the actual sum of the numbers... I don't really want to incorporate a macro either. I just assumed something easy already existed..?

[TMS]

You said:

I believe you need to use the subtotal function, but I do not understand how to use it.

And we have said that you can't use SUBTOTAL across columns to count visible columns. You can use it to count visible rows but that's not what you want.

At the moment, no one has been able to offer a viable solution.

Regards, TMS

[Richard Buttrey]

...have you tried the solution I suggested in post #7. This doesn't include a macro and is a perfectly straightforward solution based on a simple COUNTIF() function.

[XOR LX]

And a very good solution it is, Richard.

One thing I would add is that, since CELL("width",...) rounds to the nearest integer, you would get incorrect results in the unlikely event of having columns of width <0.5. So perhaps:

=MAX(0.5,CELL("width",J3))

Regards

[Richard Buttrey]

@xor

Good point. Thanks for picking that up.

[TMS]

If you put the MAX formula in cell K3, for example, and hide J3 (and recalculate), you get 0.5 being the MAX of 0.5 and 0. Is that what you were expecting?

Regards, TMS

[TMS]

You could maybe have:

Please Login or Register  to view this content.

and SUM that helper row.


Regards, TMS

[XOR LX]

Good point, TMS! Then with this method there's no way to differentiate between a hidden column and an unhidden column of width <0.5.

Perhaps a defined name with GET.CELL(16,...) should be considered?

Regards

[TMS]

A column width of 0.5 will produce a result of 1. So, it's not hidden, just very narrow but visible.

Edit: sorry. Did you see my follow up suggestion?

[XOR LX]

"=IF(CELL("width",J3),1,0)"

Of course, this also fails for the cases I mentioned previously.

Perhaps defined name, ColumnWidth, as:

=GET.CELL(16,Sheet1!J$3)

Then, in J4 and copy across:

=ColumnWidth

Regards

[TMS]

Ah, yes, sorry. Just tested 0.5, not 0.3, etc

[XOR LX]

Why Microsoft set this function to round is a mystery to me!

[REDPOOL]

I greatly appreciate the responses.
I just want to clarify a few things:
In the '=IF(CELL("width",J3),1,0)' formula, do I need to specify what "width" is, or do I copy exactly this formula and drag across all my columns?
I do not understand how to use the '=GET.CELL(16,Sheet1!J$3)' & '=ColumnWidth'. Excel does not recognize the GET.CELL function.. Can you explain what these are supposed to do? Thanks.
I apologize on my lack of proficiency - you guys are on a whole new level than me.

[XOR LX]

Hi,

GET.CELL() only functions if you use it in a defined name.

As I said in post #17, making sure the active cell in the worksheet is somewhere in column J, go to Name Manager and create a new name, ColumnWidth say, as:

=GET.CELL(16,Sheet1!J$3)

Exit Name Manager.

Then, in J4 and copy across, enter this formula:

=ColumnWidth

You can now use the totalling formula as given by Richard on this range. If you've been following our discussion, you'll have realised that the CELL("width",...) formula has certain drawbacks, though by all means use this simpler solution, though only if you are certain that you will never have any columns within that range of width<0.5.

Regards

[Richard Buttrey]

I greatly appreciate the responses.
I just want to clarify a few things:
In the '=IF(CELL("width",J3),1,0)' formula, do I need to specify what "width" is, or do I copy exactly this formula and drag across all my columns?
I do not understand how to use the '=GET.CELL(16,Sheet1!J$3)' & '=ColumnWidth'. Excel does not recognize the GET.CELL function.. Can you explain what these are supposed to do? Thanks.
I apologize on my lack of proficiency - you guys are on a whole new level than me.

Hi,

As you probably realise (and is so often the case hereabouts) new avenues and discussion points open up as time goes by. One of the advantages (some might see it as a disadvantage) of the richness of Excel is that there are often several approaches to the required result.

To answer your specific question re the '=IF(CELL("width",J3),1,0)' formula, you just enter it as it is.
The first parameter in the CELL() function (which tells you something about the cell being referred to) is a text string. In this case "width". (There are others e.g. "protect" whic tells you whether the cell is locked or not).

Here the "width" parameter will return the width of the cell mentioned in 'points'. The discussion has revolved around what happens if the width is less than 1 but not zero. If you are certain you'll never have a very tiny width column then pragmatically you may take the view that the simple approach in #7 is sufficient.

However XOR and TMS are quite correct to point out that for more certainty the defined name using the Get.Cell is better. This Get.Cell is a strange beast and has its origins in very early releases of Excel back in the 90s. It's hung on apparently ever since although not particularly well documented.

Actually, looking at it in operation in Excel 2010 (and I've no reason to doubt 2013/2007 is any different) it seems that not only is it not volatile, (i.e. calculates when any other cell on which it might be dependent changes), it doesn't change when F9 to force a calculation is used. It only seems to change when it's edited and re-entered or entered for the first time of course.

It's starting to get a bit messy I would agree but perhaps the only certain solution is to have a small macro (that runs when you click a button) which repaints the defined name that's using the Get.Cell method in cells J1:FM1. You'd need to click the button every time you changed a column width (or perhaps better still attach it to a sheet change event so that it gets repainted all the time whenever a cell (or defined cells) on the sheet changes.

Comments anyone.....?

[XOR LX]

Interesting stuff, Richard.

And quite strange. Since, if you're set to Manual Calculation mode, then F9 (or SHIFT+F9) DOES force an update of the cells containing this defined name!! (What's going on here?)

Changing it to:

=0*NOW()+GET.CELL(16,Sheet6!$J3)

will mean that F9 updates the cells in question (in Automatic Calculation mode as well), though this is hardly ideal, I agree.

Regards

[Richard Buttrey]

@Xor

I like the idea of forcing a volatile calculation with something like a 0*Now(). Good thinking. In fact that does force a change when any other cell is changed and without needing to resort to F9.

@redpool
if you're still with us then in summary pick up XOR's suggestion in #15 & 17. i.e. use the Name manager to define a name say 'columnwidth' and now define this name with XOR's last suggestion

Please Login or Register  to view this content.

Now you can enter

Please Login or Register  to view this content.

in the helper row in columns J:AF. The formula in these cells will now evaluate to zero if they are hidden, i.e. width zero. So you can now use the

Please Login or Register  to view this content.

formula to identify the number of not hidden columns. (assumes row 1 is the helper row so change as necessary