Forum Discussion

sdarnell219ymailcom's avatar
sdarnell219ymailcom
Copper Contributor
Feb 09, 2024

Excel sumifs

SUMIFS(A27:A36,C27:C36,TEXT(TODAY(),"MMM") I have one column with "dollar value", another column with calculated month "Jan" (SUMIFS will not calculate a monthly total based on a caluculated month. I have a column with calculated date, "format m-d". Another column displays month of "format m-d" as "mmm". I get 0 as a total.

excel

1 Reply

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Feb 09, 2024

    sdarnell219ymailcom If the dates in C27:C36 are real dates but custom formatted to display "Jan", "Feb" etc. SUMIFS as you have will indeed return 0 as it can't find any match between a text "Jan" and real dates.

    One option is to add a column (eg. in D) with the text representation of the dates in C as demonstrated in the picture below.

     

    Alternatively, use SUMPRODUCT as shown below:

    =SUMPRODUCT(A27:A36*(C27:C36>=EOMONTH(TODAY(),-1)+1)*(C27:C36<=EOMONTH(TODAY(),0)))

    And there are probably other ways to achieve this.