SOLVED

Summing all previous numbers with criteria

Copper Contributor

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

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

Re: Summing all previous numbers with criteria

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")``

Re: Summing all previous numbers with criteria

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

Re: Summing all previous numbers with criteria

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!

Re: Summing all previous numbers with criteria

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

Re: Summing all previous numbers with criteria

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")``