| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| hiding zero values in charts | Charts and Charting in Excel | |||
| Keeping a cell value constant trhoughout a list of values | Excel Worksheet Functions | |||
| Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
| I Need a formula to evaluate a cell with + or - values | Excel Worksheet Functions | |||
| #N/A Values : Returned by Formulas vs Entered Manually | Charts and Charting in Excel | |||
Linear Mode
