Forum Discussion

J-Des000's avatar
J-Des000
Brass 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!

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!

Resources