how to i add the first six values of of a row when in various colu
 

How do I add the first nonzero values of a row when they appear in various
columns?

I only need to add the first six values that are nonzero but these appear in
various columns throughout my table.

how to i add the first six values of of a row when in various colu
 

Try this:

For values (or blanks) in A1:J1

This ARRAY FORMULA* returns the sum of the 1st 6 non-blank values in A1:J1:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),SMALL(((A 1:J1<0)*COLUMN(A1:J1))+((A1:J1="")*10^99),{1,2,3, 4,5,6}),0))*A1:J1)

*Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Also, in case text wrap impacts the display, there are NO spaces in that
formula.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Cecilia" wrote:

How do I add the first nonzero values of a row when they appear in various
columns?

I only need to add the first six values that are nonzero but these appear in
various columns throughout my table.

how to i add the first six values of of a row when in various
 

Actually, this shorter ARRAY FORMULA also sums the 1st 6 non-blanks in A1:J1:

=SUM(A1:INDEX(A1:J1,SMALL(IF(A1:J1<"",COLUMN(A1:J 1),10^99),MIN(COUNT(A1:J1),6))-COLUMN(A1)+1))

Note 1: If there are less than 6 values, it sums the available numbers.
Note 2: That formula automatically adjusts if the range does not begin in
Col_A
Note 3: if there are N0 numbers in the range, it returns an error.

However, this array formula does not return an error if there are NO numbers
in the range:
=SUM(ISNUMBER(MATCH(COLUMN(A1:J1),SMALL(((A1:J1<" ")*COLUMN(A1:J1))+((A1:J1="")*10^99),{1,2,3,4,5,6} ),0))*A1:J1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Try this:

For values (or blanks) in A1:J1

This ARRAY FORMULA* returns the sum of the 1st 6 non-blank values in A1:J1:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),SMALL(((A 1:J1<0)*COLUMN(A1:J1))+((A1:J1="")*10^99),{1,2,3, 4,5,6}),0))*A1:J1)

*Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Also, in case text wrap impacts the display, there are NO spaces in that
formula.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Cecilia" wrote:

How do I add the first nonzero values of a row when they appear in various
columns?

I only need to add the first six values that are nonzero but these appear in
various columns throughout my table.

how to i add the first six values of of a row when in various colu
 

Assuming your data from A1:A11

=SUM(OFFSET(INDEX(A1:A11,MATCH(TRUE,A1:A11<0,0)), ,,6))

ctrlshiftenter (not just enter)

--------------------
mama no teeth

"Cecilia" wrote:

How do I add the first nonzero values of a row when they appear in various
columns?

I only need to add the first six values that are nonzero but these appear in
various columns throughout my table.

how to i add the first six values of of a row when in various
 

It needs more...

=IF(SUM(COUNTIF(A1:J1,{"<0","0"})),
SUM(A1:INDEX(A1:J1,SMALL(IF(ISNUMBER(A1:J1),
IF(A1:J1<0,COLUMN(A1:J1)-COLUMN(A1)+1)),
MIN(6,SUM(COUNTIF(A1:J1,{"<0","0"})))))),"")

which must be confirmed with control+shift+enter, not just with enter.

Ron Coderre wrote:
Actually, this shorter ARRAY FORMULA also sums the 1st 6 non-blanks in A1:J1:

=SUM(A1:INDEX(A1:J1,SMALL(IF(A1:J1<"",COLUMN(A1:J 1),10^99),MIN(COUNT(A1:J1),6))-COLUMN(A1)+1))

Note 1: If there are less than 6 values, it sums the available numbers.
Note 2: That formula automatically adjusts if the range does not begin in
Col_A
Note 3: if there are N0 numbers in the range, it returns an error.

However, this array formula does not return an error if there are NO numbers
in the range:
=SUM(ISNUMBER(MATCH(COLUMN(A1:J1),SMALL(((A1:J1<" ")*COLUMN(A1:J1))+((A1:J1="")*10^99),{1,2,3,4,5,6} ),0))*A1:J1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Try this:

For values (or blanks) in A1:J1

This ARRAY FORMULA* returns the sum of the 1st 6 non-blank values in A1:J1:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),SMALL(((A 1:J1<0)*COLUMN(A1:J1))+((A1:J1="")*10^99),{1,2,3, 4,5,6}),0))*A1:J1)

*Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Also, in case text wrap impacts the display, there are NO spaces in that
formula.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Cecilia" wrote:

How do I add the first nonzero values of a row when they appear in various
columns?

I only need to add the first six values that are nonzero but these appear in
various columns throughout my table.

how to i add the first six values of of a row when in various
 

Here's my latest in a series of final versions : \

=SUM(IF(ISNUMBER(MATCH(COLUMN(A1:J1),SMALL((A1:J1< 0)*COLUMN(A1:J1)+((A1:J1=0)+ISTEXT(A1:J1))*10^99, {1,2,3,4,5,6}),0)),A1:J1))

That one sums up to the first 6 non-zero numeric values and accounts for:
Some entries containing text
All cells containing text
Some Blanks
All Blanks
Less than 6 numeric values

Did I miss anything?
***********
Regards,
Ron

XL2002, WinXP


"Aladin Akyurek" wrote:

It needs more...

=IF(SUM(COUNTIF(A1:J1,{"<0","0"})),
SUM(A1:INDEX(A1:J1,SMALL(IF(ISNUMBER(A1:J1),
IF(A1:J1<0,COLUMN(A1:J1)-COLUMN(A1)+1)),
MIN(6,SUM(COUNTIF(A1:J1,{"<0","0"})))))),"")

which must be confirmed with control+shift+enter, not just with enter.

Ron Coderre wrote:
Actually, this shorter ARRAY FORMULA also sums the 1st 6 non-blanks in A1:J1:

=SUM(A1:INDEX(A1:J1,SMALL(IF(A1:J1<"",COLUMN(A1:J 1),10^99),MIN(COUNT(A1:J1),6))-COLUMN(A1)+1))

Note 1: If there are less than 6 values, it sums the available numbers.
Note 2: That formula automatically adjusts if the range does not begin in
Col_A
Note 3: if there are N0 numbers in the range, it returns an error.

However, this array formula does not return an error if there are NO numbers
in the range:
=SUM(ISNUMBER(MATCH(COLUMN(A1:J1),SMALL(((A1:J1<" ")*COLUMN(A1:J1))+((A1:J1="")*10^99),{1,2,3,4,5,6} ),0))*A1:J1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Try this:

For values (or blanks) in A1:J1

This ARRAY FORMULA* returns the sum of the 1st 6 non-blank values in A1:J1:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),SMALL(((A 1:J1<0)*COLUMN(A1:J1))+((A1:J1="")*10^99),{1,2,3, 4,5,6}),0))*A1:J1)

*Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Also, in case text wrap impacts the display, there are NO spaces in that
formula.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Cecilia" wrote:

How do I add the first nonzero values of a row when they appear in various
columns?

I only need to add the first six values that are nonzero but these appear in
various columns throughout my table.