Forum Discussion

pbr's avatar
pbr
Copper Contributor
Apr 04, 2025

Using formula in SUMIF criteria

I have an excel sheet as below:

AB
DateHours
1/1/20255
1/2/20256
1/3/20252
1/4/20250
1/5/20251
1/6/20253
1/7/20258
 and so on.
 In another portion of the sheet, I want to calculate the total hours per month as below
 

Is there any way to use SUMIF or SUMIFS where the criteria can use a function on the criteria column (A).   For example, I would like to use the following in criteria (below is simple code, not excel formula)

  COLUMN J = SUMIFS(B3:B367, "MONTH(a:A367) = MONTH(h1)")

I know I can use "<=EOM" etc to get the total hours.  But my main question is if it is possible to use a formula on the criteria column (ie, MONTH(column A) in the above) in SUMIF or SUMIFS

 

Formulas and Functions

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 05, 2025

    You might create a pivot table based on the range. Add the Date field to the Rows area and the Hours field to the Values area.

    Alternatively, using SUMIFS:

    D2 contains 1/1/2025, D3 contains 2/1/2025, etc.

    The formula in E2 is

    =SUMIFS($B$2:$B$366, $A$2:$A$366, ">="&D2, $A$2:$A$366, "<="&EOMONTH(D2, 0))

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Apr 05, 2025

    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.

Resources