SUM Smallest 3 Values in Non-Contiguous Cells with Uneven Intervals
SUM of Smallest 3 Non Contiguous Cells (with values above zero or non-blank cells) with Uneven Intervals
SUM Smallest 3 of 7 Non Contiguous Cells (A2, A4,A5, A7,A8, A11, A12) – Multiple Methodologies are illustrated
Cell D17 and D27 formulas: Both formulas – Sum of Smallest 3 values out of 7 Non Contiguous Cells, with uneven intervals, having values greater than zero.
———————————————————————————————–
Cell D17 and D27 formulas: Non-Contiguous cells with Uneven intervals – SUM Smallest 3 of 7 Non Contiguous Cells (A2, A4,A5, A7,A8, A11, A12), having values greater than zero.
Non-Contiguous Cells with uneven intervals (A2, A4,A5, A7,A8, A11, A12) of Column A are considered – cells may have +ive / -ive values, and blank cells are to be ignored.
Cell D17 formula:
=SUM(SMALL(IF( SUMIF( INDIRECT( B2:B8), “>0”)>0, SUMIF( INDIRECT(B2:B8), “>0”)), ROW(1:3)))
Explanation – Cell D17 formula (Array Formula – Ctrl+Shift+Enter):
1) Using the INDIRECT function refers to the range B2:B8 which contain each cell reference / address & this returns the values of the 7 non-contiguous cells. Using the SUMIF function with INDIRECT, “SUMIF(INDIRECT(B2:B8), “>0″)” returns the values from each of these non-congruous cells which are > 0 ie. {108.51; 108.13; 101; 0;0;0; 95.345} where zero is also returned for cells with values equal to or less than zero and blank cells. Using INDIRECT converts the text string into a cell reference amenable to be used as an argument for SUMIF / COUNTIF function by creating a range of cell references. The SMALL function cannot be used with this because it will consider the zero values also which need to be ignored, hence the use of “SUMIF( INDIRECT( B2:B8), “>0″)>0” which returns TRUE for values > 0 and FALSE otherwise – {TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE}.
2) Using IF with SUMIF “IF(SUMIF( INDIRECT( B2:B8),”>0″)>0, SUMIF( INDIRECT(B2:B8),”>0″))” returns all values which are greater than zero, with blank cells returning FALSE – {108.51; 108.13; 101; FALSE; FALSE; FALSE; 95.345}. Using “ROW(1:3)”, which equates {1;2;3}, as the k argument of the SMALL function returns the 3 smallest values out of these – {95.345; 101; 108.13}, & then using SUM returns the total of these 3 smallest values. Array formula (Ctrl+ Shift+ Enter).
Cell D27 formula:
=SUM(AGGREGATE( 15,6, SUMIF( INDIRECT( B2:B8), “>0”)/( SUMIF( INDIRECT(B2:B8), “>0”)>0), {1,2,3}))
Explanation – Cell D27 formula (Non-Array Formula):
1) Using the INDIRECT function refers to the range B2:B8 which contain each cell reference / address & this returns the values of the 7 non-contiguous cells. Using the SUMIF function with INDIRECT, “SUMIF( INDIRECT(B2:B8), “>0″)” returns the values from each of these non-congruous cells which are > 0 ie. {108.51; 108.13; 101; 0;0;0; 95.345} where zero is also returned for cells with values equal to or less than zero and blank cells. Using INDIRECT converts the text string into a cell reference amenable to be used as an argument for SUMIF / COUNTIF function by creating a range of cell references. The SMALL function cannot be used with this because it will consider the zero values also which need to be ignored, hence the use of “SUMIF( INDIRECT(B2:B8), “>0″)>0” which returns TRUE for values > 0 and FALSE otherwise – {TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE}.
2) Dividing the above – SUMIF( INDIRECT(B2:B8), “>0”)/( SUMIF( INDIRECT(B2:B8), “>0”)>0) – returns the array {108.51; 108.13; 101; #DIV/0!; #DIV/0!; #DIV/0!; 95.345}, where the numbers are returned for values greater than zero and error values for cells with zero or less or for blank cells (dividing with FALSE, which converts to zero on division, returns these error values). This is the array argument of the AGGREGATE function. Using 15 as the function_num argument of AGGREGATE returns the 3 Smallest values as per k argument of “{1,2,3}” (it is similar to using SMALL function), and using the options argument of 6 in AGGREGATE function ignores the error values. Using the SUM function returns the total of these 4 Largest values. Normal Non-Array formula.
—————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Other formulas:
SUM Smallest 3 of 7 Non Contiguous Cells with uneven intervals (A2, A4,A5, A7,A8, A11, A12), having values greater than zero.
Cell D5 formula: This performs the same as cell D17 formula ie. SUM Smallest 3 of 7 Non Contiguous Cells (A2, A4,A5, A7,A8, A11, A12), having values greater than zero. Uses the CHOOSE function – Array formula (Ctrl+Shift+Enter).
Cell D10 formula: This performs the same as cell D17 formula ie. SUM Smallest 3 of 7 Non Contiguous Cells (A2, A4,A5, A7,A8, A11, A12), having values greater than zero. Uses the OFFSET function with SUBTOTAL. Normal, Non-Array formula.
Cell D25 formula: This performs the same as cell D17 formula ie. SUM Smallest 3 of 7 Non Contiguous Cells (A2, A4,A5, A7,A8, A11, A12), having values greater than zero. Uses the SUMIF function with INDIRECT (references each individual cell separately). Array formula (Ctrl+Shift+Enter).
SUM Smallest 3 of 7 Non Contiguous Cells with uneven intervals (A2, A4,A5, A7,A8, A11, A12), skip blank cells.
Cell D39 formula: SUM Smallest 3 of 7 Non Contiguous Cells (A2, A4,A5, A7,A8, A11, A12), skip blank cells. Uses the SMALL function with the named range – Normal, Non-Array formula.
Cell D41 formula: SUM Smallest 3 of 7 Non Contiguous Cells (A2, A4,A5, A7,A8, A11, A12), skip blank cells. Uses the OFFSET function with SUBTOTAL, and COUNTIF / SMALL functions. Normal, Non-Array formula.
Cell D49 formula: SUM Smallest 3 of 7 Non Contiguous Cells (A2, A4,A5, A7,A8, A11, A12), skip blank cells. Uses the SUMIF / COUNTIF functions with INDIRECT (references the range which contains each cell reference), and the SMALL function. Array formula (Ctrl+Shift+Enter).
Cell D57 formula: SUM Smallest 3 of 7 Non Contiguous Cells (A2, A4,A5, A7,A8, A11, A12), skip blank cells. Uses the SUMIF / COUNTIF functions with INDIRECT (references each individual cell separately), and the SMALL function. Array formula (Ctrl+Shift+Enter).
Cell D59 formula: SUM Smallest 3 of 7 Non Contiguous Cells (A2, A4,A5, A7,A8, A11, A12), skip blank cells. Uses the SUMIF / COUNTIF functions with INDIRECT (references the range which contains each cell reference), and the AGGREGATE function. Normal, Non-Array formula.
SUM Smallest 3 of 5 Non Contiguous Cells (A2, A4,A5, A7, A8) with uneven intervals – if Non Contiguous Cells are Not Blank. The 5 Non Contiguous Cells have no blank cells.
Cell D70 formula: SUM Smallest 3 of 5 Non Contiguous Cells (A2, A4,A5, A7,A8), which do not have any blank cells. Uses the SMALL function with the named range – Normal, Non-Array formula.
Cell D72 formula: SUM Smallest 3 of 5 Non Contiguous Cells (A2, A4,A5, A7,A8), which do not have any blank cells. Uses the OFFSET function with SUBTOTAL, and SMALL function. Normal, Non-Array formula.
Cell D76 formula: SUM Smallest 3 of 5 Non Contiguous Cells (A2, A4,A5, A7,A8), which do not have any blank cells. Uses the SUMIF function with INDIRECT (references the range which contains each cell reference), and the SMALL function. Array formula (Ctrl+Shift+Enter).
Cell D57 formula: SUM Smallest 3 of 5 Non Contiguous Cells (A2, A4,A5, A7,A8), which do not have any blank cells. Uses the SUMIF function with INDIRECT (references each individual cell separately), and the SMALL function. Normal, Non-Array formula.