Using formula in SUMIF criteria

In Excel, SUMIF and SUMIFS do not support using formulas or functions directly on the range columns in the criteria (like MONTH(A:A))—they require static ranges and criteria.

However, if you are using Excel 365, you can absolutely achieve this using modern dynamic array functions like FILTER, LET, and SUM.

Using FILTER + SUM

Excel 365 lets you use dynamic array functions. You can write a formula like this:

=SUM(FILTER(B2:B100, (MONTH(A2:A100)=MONTH(H1)) * (YEAR(A2:A100)=YEAR(H1))))

This is a clean and readable alternative to SUMPRODUCT, and only available in Excel 365 and newer.

If you want total hours per month

=LET(

    months, UNIQUE(TEXT(A2:A100, "mmm-yyyy")),

    totals, BYROW(months, LAMBDA(r, SUM(FILTER(B2:B100, TEXT(A2:A100, "mmm-yyyy")=r)))),

    HSTACK(months, totals)

)

If you're making a summary table by month (e.g. Jan, Feb, etc.), you can use:

=SUMPRODUCT((TEXT(A2:A100, "yyyymm")=TEXT(H1, "yyyymm")) * B2:B100)

Or with Filter

=SUM(FILTER(B2:B100, TEXT(A2:A100,"yyyymm")=TEXT(H1,"yyyymm")))

At the end, if I may add, PivotTables are often the best and easiest solution for this kind of monthly total by date problem, especially when working with structured data.

My answers are voluntary and without guarantee!

Hope this will help you.