How to calculate the total amount per claim number using sumif in excel

Question

I have the data where there are claim numbers and then one claim number can have multiple payment. so I am trying to calculate the total amount per claim by creating a new column.

Basically try to do this sum(payment amunt) over(partition by claim no) in excel.

I have used =SUMIF(AX2:AX53800,AX2,C2:C53800) in excel but then the results are not accurate. only the first row of the same claim number is giving me the total amount, rest of the other rows for the same claim number is subtracting the amount that is present in payment amount column.

for eg; payment amt for claim number 1234 has 4 different amount - 10,20,30,40. The new column for 1234 claim number should display 100 in all the rows containing 1234 as the claim number. How do i do this in excel?

Answer 1

I assume you need to use absolute reference for criteria range and sum range. Then drag down the formula. Otherwise, when you drag the formula, reference addresses change and you get incorrect result.

=SUMIF($AX$2:$AX$53800,AX2,$C$2:$C$53800)

Answer 2

Sum By ID (Claim)

=LET(ud,AX2:AX21,vd,C2:C21,
    us,SORT(UNIQUE(TOCOL(ud,3))),
HSTACK(us,BYROW(us,LAMBDA(r,
    SUMIFS(vd,ud,r)))))

• Note that SUMIF or SUMIFS don't work with arrays.
• The following is a workaround if ud and/or vd were arrays like us is.

=LET(ud,AX2:AX21,vd,C2:C21,
    us,SORT(UNIQUE(TOCOL(ud,3))),
HSTACK(us,BYROW(us,LAMBDA(r,
    SUM(FILTER(vd,ISNUMBER(XMATCH(ud,r))))))))