Count cells containing text but ignore hidden columns

Hi

I have a worksheet with a specific character (E.g. "X")  in various cells along a row. The user has the option to hide the columns that do not apply to them (a button linked to a macro that hides the selected columns). Some of the hidden columns may contain the "X".

I am trying to use COUNTIF, COUNTA or a variation of these to count the number of cells that contain "X" in the range(D13:AR13), but ignoring the columns that have been hidden by the macro.

I have tried several suggested solutions on EE but either it ignores the hidden columns and just gives me the total number of occurrences of "X", or the wrong answer (For example, when I use =COUNTA(103,D13:AR13, I get 8 (there are actually only 7 "X"s). I have also tried using user defined functions, but these throw up 'Invalid name" errors.

I could probably do this using a macro that recounts the number of "X"s every time columns are hidden or unhidden but I'd prefer a simpler solution. Surely, it can't be that difficult to count the number of cells that contain a character, but ignore hidden columns?

The solution, whether macro driven or a formula, needs to be dynamic. I.e. when the user hides columns the new count appears instantly.

Any help greatly appreciated.

Regards

Terry
TerrygordonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TerrygordonAuthor Commented:
P.S. It's Office 365 and Excel 2016.
Rob HensonFinance AnalystCommented:
Do you have a spare row under the X row that you can make use of?

If so, in that row use this formula:
=CELL("width",D1)   where D1 refers to cell containing X

When hidden the cell width is calculated as 0.

Your count can then be:
=COUNTIFS(D1:H1,"x",D2:H2,"<>0")

D1 to H1 contains X's, D2 to H2 contains Width calculation.

Unfortunately, the column hiding/showing is not volatile so will not force a recalculation, press F9 to recalculate.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
=SUBTOTAL(103,Range) would work on rows but it doesn't work to exclude manually hidden columns.

Extract from online help for Subtotal function:
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ShumsExcel & VBA ExpertCommented:
Try below UDF:
Function CountVisibleColumns(MyRng As Range)
Dim c As Range
For Each c In MyRng
    If (c.EntireColumn.Hidden = False) And c.Value = "x" Then
        CountVisibleColumns = CountVisibleColumns + 1
    End If
Next c
End Function

Open in new window

Then in result cells type below formula:
=CountVisibleColumns(D13:AR13)

Open in new window

Rob HensonFinance AnalystCommented:
BTW - =COUNTA(103,D13:AR13) doesn't work for the following reason.

COUNTA does not have parameters only values or ranges or both. The result of 8 is coming from counting the 103 as a non-blank value plus the 7 "X" occurences.

I believe the confusion is coming from the fact that 103 is the parameter within the SUBTOTAL function to use COUNTA method.
ShumsExcel & VBA ExpertCommented:
As Rob mentioned "Unfortunately, the column hiding/showing is not volatile so will not force a recalculation, press F9 to recalculate."
I added Application.Volatile, which will not force recalculation, but you can avoid pressing F9, instead you can type/delete on any blank cell for excel to force recalculation.
Option Explicit
Function CountVisibleColumns(MyRng As Range)
Dim c As Range
Application.Volatile True
For Each c In MyRng
    If (c.EntireColumn.Hidden = False) And c.Value = "x" Then
        CountVisibleColumns = CountVisibleColumns + 1
    End If
Next c
End Function

Open in new window

TerrygordonAuthor Commented:
Hi Rob

The cell width solution seems to work, but I have to press F9, to get it to register the change.

Same problem with the user defined functions.

 Is there any way to avoid this?
ShumsExcel & VBA ExpertCommented:
Hi Terry,

As I mentioned, you can press delete in any blank cell instead of pressing F9 to recalculate. Unfortunately Hiding Columns is not Volatile by Excel itself.
Rob HensonFinance AnalystCommented:
As part of the hide columns routine you could maybe perform the count of columns and populate the count to a cell or just force a calculation at the end of the routine.
ShumsExcel & VBA ExpertCommented:
Alternatively you can add UDF formula into your routine macro:
Option Explicit
Function CountVisibleColumns(MyRng As Range)
Dim c As Range
Application.Volatile True
For Each c In MyRng
    If (c.EntireColumn.Hidden = False) And c.Value = "x" Then
        CountVisibleColumns = CountVisibleColumns + 1
    End If
Next c
End Function
Sub AddCounts()
Dim Ws As Worksheet
Dim LRow As Long, i As Long
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
Set Ws = Worksheets("Sheet1")
LRow = Ws.Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To LRow
    Ws.Range("E" & i).FormulaR1C1 = "=CountVisibleColumns(RC2:RC4)"
    Ws.Range("E" & i).Value = Ws.Range("E" & i).Value
Next i
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

Open in new window

Kindly change the range accordingly.
Check in attached...
Function_Count-Visible-Columns-with.xlsm
TerrygordonAuthor Commented:
Hi Both

Unfortunately these solutions still require an additional action by the user, whether it is a manual recalculation or one triggered by a button, which is what I was trying to avoid.

Rob, your suggestion (as part of the hide columns routine...), is what my original solution was. I was hoping to simplify this by using an Excel or UDF formula.

I have now solved the problem by using radio buttons to hide/unhide the selected parameters (columns) and a sub that counts the occurrences of the "X" in the visible columns after the hide/unhide event and populates the count result cells on the sheet.

For example:

Private Sub OptionButton1_Click()
With Sheets(1)
.Range("D:G").EntireColumn.Hidden = False
End With
countmapped
End Sub

Private Sub OptionButton2_Click()
With Sheets(1)
.Range("D:G").EntireColumn.Hidden = True
End With
countmapped
End Sub

Sub countmapped()
For i = 12 To 102
mapcount = 0
For Each c In Range(Cells(i, 4), Cells(i, 44))
If c.EntireColumn.Hidden = True Then GoTo Nxti:
If Len(c.Value) > 0 Then mapcount = mapcount + 1
Nxti:
Next c
Cells(i, 45).Value = mapcount
Next i
End Sub

Open in new window


Perhaps future editions of Excel will have a variation of the count function but it seems for now, a macro linked to the hide/unhide event is the only way to do it without having to manually recalculate.

Not sure what to do about points for this one?

Regards

Terry
ShumsExcel & VBA ExpertCommented:
Hi Terry,

I am glad you solved the problem.

If you feel we both contributed collectively, you may split points.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.