Formula for finding sum of first 3 Numbers

[santhoshkunder]

Hi All,

I want to find out the sum of first 3 non-blank cells. If there are more than 3 cells in that particular row means we should do the summation only for first 3 occurrences. Sample file attached.

Thanks
santhosha

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[santhoshkunder]

Hi ,

See if this is OK.

Narayan

Thank you very much sir. I need to apply this formula for 27000 rows. Do you think it causes any performance issue?

 

[eibi]

Santhos,

I propose the following array formula,

=SUM(IFERROR(INDEX(B4:M4,SMALL(IF(B4:M4<>"",COLUMN(B4:M4)-1),1)),0),IFERROR(INDEX(B4:M4,SMALL(IF(B4:M4<>"",COLUMN(B4:M4)-1),2)),0),IFERROR(INDEX(B4:M4,SMALL(IF(B4:M4<>"",COLUMN(B4:M4)-1),3)),0))

Paste in Cell N4, press Ctrl+Shift+Enter to calculate as array, drag to fill all 27000 rows.

Attached.

 

[John Jairo V]

Hi to all!

You can use this formulae too (Array Entry - CSE):
=SUM(INDEX(B4:M4,N(IF(1,SMALL(IF(B4:M4<>"",COLUMN(B4:M4)-MIN(COLUMN(B4:M4))+1),ROW(INDIRECT("1:"&MIN(COUNT(B4:M4),3))))))))

Blessings!

 

[eibi]

Santhos,

We recently had a very similar need in our office, and I found another solution (following on John Jairo's):

=SUM(INDEX(A4:M4,,N(IF(1,IFERROR(SMALL(IF(B4:M4>0,COLUMN(B4:M4),""),{1,2,3}),0)))))

Still an array formula, but considerably more concise than my first suggestion. Sorry so late...

See attached.

 

[bines53]

Hi Santhos,

It is also possible without CSE ,
=SUM(B4:INDEX(INDEX(B4:M4,,AGGREGATE(15,6,COLUMN(B4:M4)-1/(B4:M4<>""),IF(COUNT(B4:M4)>3,3,COUNT(B4:M4)))),,0))

David