Sum operation under many conditions

ajl_ahmed 

I want just to underscore the excellent point that Patrick2788 makes:

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.

In emphasizing this point, I'd add that you should always avoid merging cells at the "raw data" or "input" end of a process, which is what we're looking at in your spreadsheet. That's where you have the data to be worked on to yield the desired result. Merged cells at that stage of an analysis process are both potentially problematic and essentially unnecessary to begin with.

Where merged cells can be legitimately used and helpful is at the output end, in a final report or outcome, to make things look clearer. And while we're on the topic of fancy formatting, this same guideline should be followed with colors and other features; save them for the output. Keep raw data clean and basic; let Excel do its magic via formulas, pivot tables, dynamic arrays, whatever....and then, when the results are ready to display, use whatever highlighting is useful in making the desired points. But even then, don't overdo it.

This principle--one aspect of distinguishing between input and output--is fundamental to good spreadsheet design.

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)
        )
    );