Forum Discussion

J-Des000's avatar
J-Des000
Brass Contributor
Jun 25, 2024

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-10-09     2024-11-18   2024-10-09

50                     100                 30                     75                90

 

Output all summed previous numbers as a horizontal spilled range that are equal to 2024-11-18

Output:

2024-11-18       2024-05-12      2024-10-09      2024-11-18     2024-10-09

50                         n/a                    n/a                  125                 n/a

 

If it could also display n/a or 0 as a value that would be great (e.g changeable between both). Thanks

 

 

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!

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jun 25, 2024

    J-Des000 

    = LET(
    criterion?,     date = initialDate,
    requiredAmount, IF(criterion?, amount),
    accumulated,    SCAN(0, requiredAmount, SUM),
    IF(criterion?,  accumulated, "n/a")
  )

    You might need to use

    LAMBDA(x, y, SUM(x, y))

    in place of SUM.

    • J-Des000's avatar
      J-Des000
      Brass Contributor
      Jun 25, 2024
      The variable "amount" doesn't look like it's defined in this function....can you provide a workbook example with the following function? Much appreciated!
  • 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
      Brass 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