I thought SUBTOTAL would help, but it only omits FILTERED rows, not grouped rows that have been hidden. This looks like a job for Aladin, or one of the other formula gurus...
However, here's a custom function that does the job.
Dim X As Double
Dim c As Range
X = 0
For Each c In Target
If c.RowHeight = 0 Then
X = X
X = X + c.Value
SUBTOTAL_VIS = X
Paste this code into a new module.
Enter the formula using the function wizard (Fx) and selecting User Defined Functions. Target is the range that you want to sum.
To exclude grouped values from the total, use the outline buttons to collapse the outline.
Note: this code will need to be placed in the workbook where you want to use it. You'll also need to recalc the sheet (Shift + F9) when you change the outline.