Print

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

SUM Largest 4 Values in Non-Contiguous Cells of a Row

 


 

SUM Largest 4 Values in Non-Contiguous Cells of a Row 

 

Cell B9 formula: Non-Contiguous cells with even intervals - SUM largest 4 values in the 2nd row cells of B2, F2, J2, N2, R2 & V2 (consider 1st cell, skip the next 3 cells). Returns SUM of cells B2, F2, J2 & N2. 

    

-----------------------------------------------------------------------------------------------

 

Sum Largest 4, Non-Contiguous Cells  

 

Cell B9 formula: Non-Contiguous cells with even intervals - SUM largest 4 values in the 2nd row cells of B2, F2, J2, N2, R2 & V2 (consider 1st cell, skip the next 3 cells). Returns SUM of cells B2, F2, J2 & N2. 

 

The formula uses "TRANSPOSE(ROW(1:4))" instead of "{1,2,3,4}" which is useful when determining a large number of "Largest" values.

 

Note: The formula presumes there will be a minimum of 4 values to be considered by the LARGE function, and in case there are less then IFERROR function can be used (to return zero) to avoid getting an error result.

 

Non-Contiguous Cells of the 2nd Row (B2:X2) are considered - cells may have +ive / -ive values, and blank cells are to be ignored.

  

Cell B9 formula: 

  

=SUM(LARGE(IF( B2:X2<>"", IF(MOD( COLUMN(B2:X2)- COLUMN(B2)+1,4)=1, B2:X2)), TRANSPOSE(ROW(1:4))))   

  

Explanation - Cell B9 formula (Array Formula - Ctrl+Shift+Enter):

 

1) MOD(COLUMN(B2:X2)- COLUMN(B2)+1, 4): Using "COLUMN(B2:X2)- COLUMN(B2)+1" as the number argument of the MOD function returns an array of numbers for each cell in the range B2:X2, from 1 to 23 - {1,2, 3,4, 5,6, 7,8, 9,10, 11,12, 13,14, 15,16, 17,18, 19,20, 21,22, 23}. Using 4 as the divisor, the MOD function returns an array of numbers for each cell in the range B2:X2 - {1,2, 3,0, 1,2, 3,0, 1,2, 3,0, 1,2,3,0, 1,2, 3,0, 1,2,3}. Equating this array with 1 (which will refer cells  B2, F2, J2, N2, R2 & V2), returns an array of TRUE and FALSE values - {TRUE, FALSE, FALSE, FALSE,TRUE, FALSE, FALSE, FALSE, TRUE, FALSE,..} - TRUE indicates every 4th cell in the range starting with B2 ie. cells B2, F2, J2, N2, R2 & V2.

 

2) IF(B2:X2<>"", IF(MOD( COLUMN(B2:X2)- COLUMN(B2)+1,4)=1, B2:X2)): using the IF statement with the array of 1) above, returns the values of cells  B2, F2, J2, N2, R2 & V2 from the range B2:X2 - {-14,FALSE, FALSE, FALSE,-9, FALSE, FALSE, FALSE,-28, FALSE, FALSE, FALSE, -16,FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, -33 ,FALSE, FALSE}. This is also the array argument of the LARGE function. Using "TRANSPOSE(ROW(1:4))" as the k argument of the LARGE function returns the 4 largest values - {-9,-14, -16,-28}, and using SUM returns their total ie. -67 in cell B9.

 

3) Using "TRANSPOSE(ROW(1:4))" returns "{1,2,3,4}" and is used as the k argument of the LARGE function to determine the largest 4 values. This option is particularly useful when determining a large number of "Largest" values (say, to determine 12 largest values) wherein "TRANSPOSE(ROW(1:12))" can be used which obviates the need of entering an array of {1,2,3,4, 5,6,7,8, 9,10, 11,12} manually.

 

 

-----------------------------------------------------------------------------

 

Related Formulas - illustrated in the Ebook of Excel Formulas.

 

Other formulas:

 

Cell B8 formula: This performs the same as cell B9 formula ie. Non-Contiguous cells with even intervals - SUM largest 4 values in the 2nd row cells of B2, F2, J2, N2, R2 & V2 (consider 1st cell, skip the next 3 cells). Returns SUM of cells B2, F2, J2 & N2. Uses "{1,2,3,4}" as the k argument of the LARGE function - Array formula (Ctrl+Shift+Enter).

 

Cell B12 formula: SUM largest 4 values in the 2nd row (B2:X2) where 1st row (B1:X1) = 1. Returns SUM of cells C2, F2, J2 & O2. Uses the AGGREGATE function. Normal, Non-Array formula.

 

Cell B13 formula: This formula performs the same as cell B12 formula, SUM largest 4 values in the 2nd row (B2:X2) where 1st row (B1:X1) = 1. Uses the LARGE function instead of AGGREGATE. Returns SUM of cells C2, F2, J2 & O2. Array formula (Ctrl+Shift+Enter).  

 

Cell B16 formula: Non-Contiguous cells with even intervals - SUM largest 4 values in 2nd row cells of B2,C2, F2,G2, J2,K2, N2,O2, R2,S2, V2,W2 (consider 1st 2 cells, skip next 2 cells). Returns SUM of cells F2, G2, O2 & S2. Array formula (Ctrl+Shift+Enter).

 

Cell B19 formula: SUM largest 4 values in the 2nd row (B2:X2) where 1st row (B1:X1) = 1 or 2. Returns SUM of cells D2, G2, O2 & X2. Uses the AGGREGATE function. Normal, Non-Array formula. 

 

Cell B23 formula: Non-Contiguous cells with even intervals: Consider 6 blocks of 2 cells each (consider 1st 2 cells, skip next 2 cells) in the 2nd row - B2,C2 & F2,G2 & J2,K2 & N2,O2 & R2,S2 & V2,W2. Compute Largest of the 2 values from each Block - 6 values. Then Return the Largest 4 of these 6 values. Returns SUM of cells B2, G2, O2 & S2. Array formula (Ctrl+Shift+Enter).