Sum n values in sparse dataset

user_5935076 0
Viewed 99

I have a sparse dataset in excel, e.g.:

1 0 2 4 5 8

2 3 0 0 0 6

Zeros represent missing values.

I want to sum the first 3 nonmissing values in each row using Excel.

Thanks

excel
4 Answers

For a sum against EACH row, you can do as below:

Array formula - use:

Ctrl+Shift+Enter

=SUM(INDEX($A1:$F1, 1, 1):INDEX($A1:$F1, 1, SMALL(IF($A1:$F1, COLUMN($A1:$F1) - COLUMN($A1) + 1), 3)))

enter image description here Image shows second row selected, but formula I typed shows first row.

user_51759420

For a normally entered formula, try:

=SUMPRODUCT(N(OFFSET(A1,0,AGGREGATE(15,6,1/1/(A1:K1<>0)*COLUMN(A1:K1),{1,2,3})-1)))
user_28729220

I don't have Excel at home to test it, but you can try entering this formula with Ctrl+Shift+Enter:

=SUM(OFFSET(A1:F1,0,0,1,SMALL(IF(A1:F1,COLUMN(A1:F1)),3)))

The SMALL(IF(A1:F1>"",COLUMN(A1:F1)),3) part should return the index of the third non-zero cell.

user_13831680

If you have the latest version of Excel 2016 and you know for sure that each cell will only contain one digit, you can leverage the CONCAT Function:

=SUM(--MID(SUBSTITUTE(CONCAT(A1:F1),0,""),{1,2,3},1))
user_45674110
Related