Only apply formula for visible cells isn't working

Hi guys,

been trying to fix my problem for the last couple of days but weren't able to do so...
My worksheet looks like the following:

___________|_Product1_|_Product2_|_Product3_|_Product4_|
Shop1      |    X     |     X    |          |          |
Shop2      |          |     X    |     X    |          |
Shop3      |    X     |          |          |     X    |
           |          |          |          |          |
Ingredient1|          |     X    |          |     X    |
Ingredient2|    X     |     X    |     X    |     X    |
Ingredient3|    X     |          |     X    |          |

I'm using a macro which calls other macros depending on a data validation list selection. The macro looks like the following:

Sub Shop1()
Dim LastColumn As Long, x As Long
LastColumn = ActiveSheet.Cells(3, Columns.Count).End(xlToLeft).Column
For x = 4 To LastColumn
   If UCase(Cells(7, x).Value) = "X" Then
     Columns(x).Hidden = False
   Else
     Columns(x).Hidden = True
   End If
Next
End Sub

I'm counting all 'X' in row 7 beginning from column D to the last column (which is AF in my case). All columns without an 'X' are hidden.
It works as indented but a formula in my sheet gives me headache. It's this one:

=SUM(COUNTIF(D7:AF7;{"X";"O"}))/COUNTA($D$2:$AF$2)

I'm calculation the percentage of occuring 'X' and 'O' with it. The problem starts when I use the macro to hide some columns (which aren't marked with 'X') as the percentage stays the same -> it's still calculating it for all existing columns.
What I wanted to achieve is a changing percentage based on the amount of VISIBLE columns. I tried to use sumproduct and subtotal but always ended up with #ref! or #value! errors, such as this one:

=SUMPRODUCT(SUBTOTAL(103;INDIRECT(COLUMN(D:AF)&"7"))*(D7:AF7={"X";"O"}))/COUNTA($D$2:$AF$2)

Hope you can guide me to the right direction
Thanks in advance.

Cheers,
Mike

I'm not really following what you're trying to accomplish. if you hide everything that doesn't have an X in it and then you want the % of visible cells with an X in it, isn't that always going to be 100%?

also, based on your sample data if there is anything in the cell it's an X, won't that also always calculate 100%

I already thought it may sound really complex. I therefore attached a sample which hopefully answers all open questions.

interesting... i'd have expected to be able to use subtotal to help you out, but apparently subtotal does not like embedded formulas... how do you feel about changing X's to 1's? can make it look like an X with formatting if that's important to you... not sure what O's represent, but i guess those would have to be 1's as well for the solution i have in mind.

another thought is to use the same macros you're calling when a shop is selected to calculate those %'s... something like:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(True, True) = "$A$2" Then
'simarui additional code
        Dim LastCol As Long, x As Long, ItemCount As Long, VisibleCols As Long, LastRow As Long, rng As Range
        ItemCount = 0
        VisibleCols = 0
        LastCol = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
        LastRow = Range("A" & Rows.Count).End(3).Row
'end of simarui code
        Select Case Range("A2")
        'Target
            Case "All"
                Call ALL
            Case "Shop1"
                Call Shop1
            Case "Shop2"
                Call Shop2
            Case "Shop3"
                Call Shop3
            Case "Shop4"
                Call Shop4
            Case "Shop5"
                Call Shop5
            Case "Shop6"
                Call Shop6
            Case Else
                'Do nothing
        End Select
'simarui additional code
        For Each rng In Range("A12:A" & LastRow)
        ItemCount = 0
        VisibleCols = 0
            For x = 4 To LastCol
                
                If Columns(x).Hidden = False Then
                    VisibleCols = VisibleCols + 1
                    If UCase(Cells(rng.Row, x)) = "X" Or UCase(Cells(rng.Row, x) = "O") Then _
                        ItemCount = ItemCount + 1
                End If
            Next
            Range("B" & rng.Row) = ItemCount / VisibleCols
        Next rng
'end of simarui code
    End If
End Sub