Search code examples
excelwps

SumIF formula for calculating cells based on today() criteria

enter image description here

I am trying to calculate plan till date based on the sheet above. For instance, let say today is 5th of May. Then it should only sum plan target before and this date. So it should count 900+900+700+800.

I want it to use today() criteria so I don't have to manually edit anything. Is this possible? Let me know if you need any information, thanks!

Solution

  • You could try using the following formula :

    enter image description here

    • Formula used in cell D17

    =SUMIF($C$4:$R$4,"<="&$D$16,INDEX($C$5:$R$9,MATCH($B17,$B$5:$B$9,0),0))

    Or,

    • Formula used in cell C17

    =SUMPRODUCT(($B$5:$B$9=$B17)*($C$4:$R$4<=$C$16)*$C$5:$R$9)

    Or, If you are using MS365 then with Dynamic Spill Array formulas.

    enter image description here

    • Formula used in cell E17

    =MAP(B17:B20,LAMBDA(m,SUM(FILTER($C$5:$R$9,m=$B$5:$B$9)*($C$4:$R$4<=E16))))

    Note: C16 D16 & E16 refers to TODAY()