Search code examples
excelfunctionformulacriteriasumifs

How can I apply Month function inside a criteria interval in the SUMIFS excel function?

I'm using excel to do budgeting and I have this situation: I have a grid with expense categories as rows and months as columns and I write expenses in the right list, this is an example:

enter image description here

Using a column for the month I can easily automatically do the sum for month inside the grid. Every cell of the grid has this formula:

=SOMMA.PIÙ.SE($G$2:$G$50;$F$2:$F$50;$A2;$I$2:$I$50;MESE(DATA.VALORE(B$1&"1")))

In english it must be something like this (I'm italian):

=SUMIFS($G$2:$G$50,$F$2:$F$50,$A2,$I$2:$I$50,MONTH(DATEVALUE(B$1&"1")))

This works, but I would like to remove the I column and automatically take the month values of the dates in the criteria interval, is it possible?

I've tried also using MAP function this way:

=SUMIFS($G$2:$G$50,$F$2:$F$50,$A2,MAP($H$2:$H$50,LAMBDA(a,MONTH(a))),MONTH(DATEVALUE(B$1&"1")))

But neither this way it works.

Solution

  • If I understand correctly, you will need to include an explicit reference to the year, e.g.:

    =LET(ξ,DATEVALUE(B$1&23),SUMIFS($G$2:$G$50,$F$2:$F$50,$A2,$H$2:$H$50,">="&ξ,$H$2:$H$50,"<"&EDATE(ξ,1)))

    If 2023 is not the appropriate year, replace the 23 in the part

    DATEVALUE(B$1&23)

    as required.