SUM – Assign separate Values to Non-Contiguous Cells and return SUM
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; .
———————————————————————————————–
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.
—————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
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).