Forum Discussion

J-Des000's avatar
J-Des000
Copper Contributor
Jun 25, 2024
Solved

Summing all previous numbers with criteria

Hi again,   Looking to sum all previous numbers based on criteria. Need output to be a horizontal spilled range.. Example below. Tried using TAKE function   Ex 2024-11-18      2024-05-12    2024...
excel
Excel on Mac
Formulas and Functions
  • dscheikey's avatar
    dscheikey
    Jun 25, 2024

    J-Des000 

    I have a solution, but I don't know if there is an easier way.

     

    Spilled Formula:

    =LET(a,OFFSET(A1,0,0,1,SEQUENCE(1,5)),b,OFFSET(A2,0,0,1,SEQUENCE(1,5)),IF(A1:E1=F6,SUMIF(a,F6,b),"n/a"))

     Whithout Spilling:

    =IF(A1=$F4,SUMIF($A1:A1,$F4,$A2:A2),"n/a")

    See also my enclosed example!

dscheikey's avatar
dscheikey
Bronze Contributor
Jun 25, 2024

J-Des000 

I have a solution, but I don't know if there is an easier way.

 

Spilled Formula:

=LET(a,OFFSET(A1,0,0,1,SEQUENCE(1,5)),b,OFFSET(A2,0,0,1,SEQUENCE(1,5)),IF(A1:E1=F6,SUMIF(a,F6,b),"n/a"))

 Whithout Spilling:

=IF(A1=$F4,SUMIF($A1:A1,$F4,$A2:A2),"n/a")

See also my enclosed example!

J-Des000's avatar
J-Des000
Copper Contributor
Jun 25, 2024
Spilled formula works but trying to look for something without using "OFFSET" function as it's volatile. Thanks again for the help.

Resources