Sum operation under many conditions

ajl_ahmed 

A few observations and then a solution:

1. The merging of cells to show Subject 1 and Subject 2 does us no favors. It's best to avoid merging if at all possible.  Excel has the functions to workaround merged cells in formulas but it's best to avoid it if possible.

2. The data is already pivoted - rows, columns (dates and subjects), and values - a cross tab.

Presumably your actual data set is much larger than the sample shared. My approach is to first unpivot the data and then summarize with PIVOTBY.

=LET(
    flattened, UnPivotM(A4:A6, B2:I2, B4:I6),
    row_label, TAKE(flattened, , 1),
    col_label, CHOOSECOLS(flattened, 2),
    val, TAKE(flattened, , -1),
    PIVOTBY(row_label, col_label, val, SUM)
)

Where  UnPivotM is a dedicated function I've created to unpivoting matrices. The workbook attached contains a copy of the function:

UnPivotM = LAMBDA(rows, columns, values,
        LET(
            i, ROWS(rows),
            j, COLUMNS(rows),
            c, COLUMNS(columns),
            s, TOCOL(SEQUENCE(, j) * SEQUENCE(c, , 1, 0)),
            row_labels, WRAPROWS(TOCOL(CHOOSECOLS(rows, s)), j),
            attribute, TOCOL(CHOOSEROWS(columns, SEQUENCE(i, , 1, 0))),
            v, TOCOL(values),
            HSTACK(row_labels, attribute, v)
        )
    );