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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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(((A1: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
 
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(((A1: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
 
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:J1),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 said:
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(((A1: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 said:
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.
Click to expand...
Click to expand...
 
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:J1),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 said:
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(((A1: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 said:
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.
Click to expand...
Click to expand...
 
Assuming your data from A1:A11

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

ctrl>shift>enter (not just enter)
 
Assuming your data from A1:A11

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

ctrl>shift>enter (not just enter)
 
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 said:
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:J1),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 said:
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(((A1: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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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:J1),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 said:
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(((A1: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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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:J1),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 said:
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(((A1: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


:

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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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:J1),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 said:
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(((A1: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


:

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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Counting Zeros in a row until encounter first non-zero value 9
Automatic rows creation with various permutation of column values 2
find first nonzero value in column 2
find first nonzero value in column 5
nonzero value 2
Zero-Length string vs Zero (also a valid value) 2
Excel Help to copy information from one row at a time to another sheet 1
lookup nonzerovalues 2

Back
Top