User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

SUM - Assign separate Values to Non-Contiguous Cells and return SUM

 

 

Related Links: 

1. Excel VLOOKP Function, with examples.

2. Left Lookup with VLookup Excel function.

3. Left Lookup, with Index, Match & Offset Excel functions.

4. Vlookup Multiple Values - Return MULTIPLE corresponding values for ONE Lookup Value.

 


 

SUM - Assign separate Values to Non-Contiguous Cells in a column, and return SUM

 

 

Cell E4 formula: Check ALL cells in the range A2:A10 for "Yes" & return SUM: if 2nd or 8th cells are "Yes" add 10 for each; if any other cells are "Yes" add 5 for each; . 

   

 

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

 

 

Sum, separate Values for Non-Contiguous Cells

          

"Open Image in New Tab" for a full and clear view. 

 

 

Cell E4 formula: Check ALL cells in the range A2:A10 for "Yes" & return SUM: if 2nd or 8th cells are "Yes" add 10 for each; if any other cells are "Yes" add 5 for each; 

 

 

Cell E4 formula: 

  

=SUMPRODUCT((A2:A10= "Yes")*( ISNUMBER( MATCH( ROW(A2:A10)- ROW(A2)+1, {2,8},0))*5+5))  

  

Explanation - Cell E4 formula (Non-Array Formula):

 

1) MATCH( ROW(A2:A10)- ROW(A2)+1, {2,8},0): returns an array of numbers and error values - {#N/A; 1;#N/A; #N/A; #N/A; #N/A; #N/A;2; #N/A}. "{2,8}" is the lookup_array argument of the MATCH function. "ROW(A2:A10)- ROW(A2)+1" is the lookup_value argument of the MATCH function and returns the row numbers / positions for the range A2:A10 {1;2;3; 4;5;6; 7;8;9}. Using the MATCH function matches the numbers 2 & 8, and returns 1 for the row number which matches "2" (row number 2 in the 2nd position returns 1) and returns 2 for the row number which matches "8" (row number 8 in the 8th position returns 2). Error values are returned for row numbers which are not 2 or 8 ie. non-matches. Using ISNUMBER with the MATCH function returns an array of TRUE and FALSE values, where TRUE is for these numbers - {FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}.

 

2) ISNUMBER( MATCH( ROW(A2:A10)- ROW(A2)+1, {2,8},0))*5: returns an array of 5s and zeros - {0;5; 0;0; 0;0; 0;5;0} - where 5 is returned for every TRUE in 1) above ie. for the 2nd and 8th cells in the range A2:A10. Using (ISNUMBER( MATCH( ROW(A2:A10)- ROW(A2)+1, {2,8},0))*5+5) returns an array of 5s and 10s - {5;10; 5; 5;5; 5;5; 10;5} - 10s are for the 2nd and 8th positions in the range A2:A10, while 5s are for all other positions.

 

3) (A2:A10="Yes"): returns an array of TRUE and FALSE values - {TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE} - the TRUE is for each cell / position which contains "Yes". Multiplying this array with the above array of 10s and 5s in 2) above  will return 10 for each occurrence of "Yes" in 2nd and 8th position and 5 for each occurrence of "Yes" in any other position - {5; 10;0; 5;0; 5;5; 0;5}. Using SUMPRODUCT returns the total of all these 10s and 5s ie. 35 in cell E4.  

 

 

Other formulas:

 

Cell E5 formula: This Cell E5 formula performs the same as cell E4 formula, Check ALL cells in the range A2:A10 for "Yes" & return SUM: if 2nd or 8th cells are "Yes" add 10 for each; if any other cells are "Yes" add 5 for each. Uses the IF statement - Array formula (Ctrl+Shift+Enter).

 

Cell E8 formula: Check only SPECIFIC cells in the range A2:A10 for "Yes" & return SUM: if 2nd or 8th cells are "Yes" add 10 for each; if 1st, 3rd, 7th or 9th cells are "Yes" add 5 for each. Normal, Non-Array formula.

 

Cell E9 formula: This Cell E9 formula performs the same as cell E8 formula, Check only SPECIFIC cells in the range A2:A10 for "Yes" & return SUM: if 2nd or 8th cells are "Yes" add 10 for each; if 1st, 3rd, 7th or 9th cells are "Yes" add 5 for each. Uses the IF statement - Array formula (Ctrl+Shift+Enter).  

 

 

    

Only one of the multiple formulas is illustrated above. For full details and explanation refer to Ebook of Excel Formulas.