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