Sum a row in Excel with #NUM! values

Question

I need to calculate running totals of sum events. The "score" calculations are nested if functions that will return #NUM! until values are placed in the distance columns. The total column represents a running total.

Sample image of workbook

Sample image of workbook

This is the nested IF() function that is returning a #NUM! unless numbers are inputted.

=IF(G5=LARGE(G$5:G$16,1),35,
  IF(G5=LARGE(G$5:G$16,2),30,
  IF(G5=LARGE(G$5:G$16,3),25,
  IF(G5=LARGE(G$5:G$16,4),20,
  IF(G5=LARGE(G$5:G$16,5),15,0)))))

I've tried setting the Total with SUM(), SUMIF() with the middle value >0, i've tried SUMIF() disregarding NA, but I can't seem to find the right formula to do a running total and ignore the #NUM! values.

SUM with Nest If and IF NA

Use AGGREGATE() function which has capability to ignore errors. Your screenshot doesn't make sense which are input and which are desired result. — Harun24hr
– Harun24hr, Commented Jun 14, 2024 at 2:16
Thank you! I think I solved it by adding a =ifblank(cell, 0, nested ifs) — Josh Budda Ortega
– Josh Budda Ortega, Commented Jun 14, 2024 at 2:39

Mayukh Bhattacharya · Accepted Answer · 2024-06-14 02:36:52Z

0

If I have understood correctly, you are probably looking for a running total by ignoring the multiple error values for each column per row wise, if so could try using the following formula:

• Formula used in cell L3

=SCAN(0,LET(δ, CHOOSECOLS(B3:K7,SEQUENCE(,10/2,2,2)), 
  BYROW(IF(ISERROR(δ),0,δ),SUM)),SUM)

^{NOTE: The above Excel Formula exclusively works with MS365 Office Insiders Version, and doesn't uses LAMBDA() construct hence its called as ETA LAMBDA(), for without Office Insiders version could try the following one:}

=TAKE(SCAN(0,LET(δ, CHOOSECOLS(B3:K7,SEQUENCE(,10/2,2,2)), 
  IF(ISERROR(δ),0,δ)),LAMBDA(r,c,r+c)),,-1)

Please ensure to adapt the cell references and ranges accordingly as per your suit.

answered Jun 14, 2024 at 2:36

Mayukh Bhattacharya

29.2k9 gold badges31 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sum a row in Excel with #NUM! values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related