Excel: Count Weighed Cells

I've an excel-spreadsheet, which is my timetable. this means, that I've some tasks, which is almost always a connected cell, sometimes I connect 20 cells, sometimes only two. Example

Since every cell is one hour I wanted to count the weighed cellcount. This means: If I have a cell witch is only one cell, it counts as one, if I have 3 connected it shall count as 3. So in the example Picture, the cellcount shall be S1: 3, S2: 4, S3:2, S4:6

I've been searching a long time, but haven't found any function which does solve the problem. The best I've found, is =countif(B4:H19, "Subject1"), but here a cell based on three conected cells counts as one. Could you please help me solve this problem?

Thank you for your answer in advance!

Actually the screen shot you have shared with us has both Merged & Non-merged cells, therefore instead of any formula this issue needs VBA macro.

   Function MergedCellCount(ByRef Rng As Range, ByVal Criteria As Variant)

    Dim c       As Long
    Dim Cell    As Range
    Dim n       As Long
    Dim r       As Long
    
        Application.Volatile
        
        For c = 1 To Rng.Columns.Count
            For r = 1 To Rng.Rows.Count
                Set Cell = Rng.Cells(r, c)
                If Cell.MergeCells = True And (Rng.Columns(c).Column = Cell.MergeArea.Column) Then
                    If Cell = Criteria Then
                        n = n + Cell.MergeArea.Count
                        r = r + (Cell.MergeArea.Rows.Count - 1)
                    End If
                End If
            Next r
        Next c
        
        MergedCellCount = n
        
    End Function

    Function unMergedCellCount(ByRef Rng As Range, ByVal Criteria As Variant)

    Dim c       As Long
    Dim Cell    As Range
    Dim n       As Long
    Dim r       As Long
    
        Application.Volatile
        
        For c = 1 To Rng.Columns.Count
            For r = 1 To Rng.Rows.Count
                Set Cell = Rng.Cells(r, c)
                If Cell.MergeCells <> True And (Rng.Columns(c).Column = Cell.MergeArea.Column) Then
                    If Cell = Criteria Then
                        n = n + Cell.MergeArea.Count
                        r = r + (Cell.MergeArea.Rows.Count - 1)
                    End If
                End If
            Next r
        Next c
        
        unMergedCellCount = n
        
End Function

How it works:

• Either press Alt+F11 or select the TAB then Right click and from the menu hit View code.

• Copy & Paste these VBA macro.

• Press Alt+Q to return to the Sheet.

• Now Save the Workbook as Macro Enabled File *.xlsm.

• Enter this formula in cell K2 & fill down.

  =MergedCellCount(H$2:H$17,J2)+unMergedCellCount(H$2:H$17,J2)

• Adjust cell references as needed.