SOLVED

Summing all previous numbers with criteria

Copper Contributor

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

 

 

4 Replies
best response confirmed by J-Des000 (Copper Contributor)
Solution

@J-Des000 

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

 

dscheikey_0-1719339692599.png

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 

= 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.

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!
Spilled formula works but trying to look for something without using "OFFSET" function as it's volatile. Thanks again for the help.
1 best response

Accepted Solutions
best response confirmed by J-Des000 (Copper Contributor)
Solution

@J-Des000 

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

 

dscheikey_0-1719339692599.png

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!

View solution in original post