User Rating: 5 / 5     SUMIF for Non-Contiguous Cells, SUMIF with Wildcard Characters, SUMIF for Date Range(s)

SUMIF for Non-Contiguous Cells in "Range" or "Criteria", SUMIF with Wildcard Characters, SUMIF for Date Range(s)

Column I formulas use Sumif or Sumproduct functions to do a SUMIF for Non-Contiguous Cells in "Range" or "Criteria", SUMIF with Wildcard Characters, SUMIF for Date Range(s). Out of 19 formulas in column I, 4 formulas are being illustrated here.

Cell I4 formula: Using SUMIF with CHOOSE function: SUM col D where Col B is EITHER cell F4 OR cell G5 OR cell H6 (ie. 3 non-contiguous cells as criteria).

Cell I15 formula: SUMIF for the 3 non-contiguous ranges "B2:B5", "B9" & "B14:B16" for "Car" criteria.

Cell I28 formula: Uses wildcard Asterisk (*) which matches any sequence of characters: SUM col D for col C values starting with "U" where col B cells are non-blank.

Cell I38 formula: Using SUMIF for Dates within a Date Range - SUM col D where Column A dates are between 1st May 2017 AND 31st July 2017.

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

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

Non-Contiguous "Criteria" Cell References in SUMIF - Cell I4 formula uses SUMIF with CHOOSE function: SUM col D where Col B is EITHER cell F4 OR cell G5 OR cell H6. (ie. 3 non-contiguous cells as criteria).

Cell I4 formula:

=SUMPRODUCT( SUMIF( B2:B20, CHOOSE({1,2,3}, F4, G5,H6), D2:D20))

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

1) Using the CHOOSE function - CHOOSE({1,2,3}, F4, G5,H6) - returns the 3 chosen values (1 to 3) of the 3 cells mentioned ie. {"Car","Metro","Bike"}.

Non-Contiguous Cell References for "Range" in SUMIF - Cell I15 formula uses SUMIF with INDIRECT to SUM the 3 non-contiguous ranges "D2:D5", "D9" & "D14:D16" for "Car" criteria in 3 non-contiguous ranges "B2:B5", "B9" & "B14:B16".

Cell I15 formula:

=SUMPRODUCT(SUMIF( INDIRECT( RngNonContg), "Car", INDIRECT( RngNonContg_Offset)))

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

1) Uses named range "RngNonContg" (F12:F14) to search the 3 non-contiguous ranges "B2:B5", "B9" & "B14:B16" for "Car" criteria, and uses another named range "RngNonContg_Offset" (G12:G14) for the SUM range ("D2:D5", "D9" & "D14:D16").

Using SUMIF with wildcard characters - Cell I28 formula does a SUMIF using wildcard Asterisk (*): SUM col D for col C values starting with "U" where col B cells are non-blank.

Cell I28 formula:

=SUMIFS(D2:D20, C2:C20, "U*", B2:B20, "<>")

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

1) Using the wildcard character Asterisk (*) which matches any sequence of characters.

Using SUMIF with Dates Range - Cell I38 formula uses SUMIF for Dates within a Date Range - SUM column D where Column A dates are between 1st May 2017 AND 31st July 2017

Cell I38 formula:

=SUMIFS(D2:D20, A2:A20, ">="& DATE(2017,5,1), A2:A20, "<="& DATE(2017,7,31))

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

1) Uses the DATE function to determine that dates are between 1st May 2017 AND 31st July 2017.

Other formulas:

Non-Contiguous "Criteria" Cell References in SUMIF

Cell I6 formula: SUMIFS formula, Hardcoded arguments: SUM col D where Col A = cell F2 AND Col B is EITHER "Car" OR "Metro" OR "Bike".

Cell I7 formula: Using SUMIFS with CHOOSE function: SUM col D where Col A = cell F2 AND Col B is EITHER cell F4 OR cell G5 OR cell H6..

Cell I8 formula: Using multiple SUMIFS: SUM col D where Col A = cell F2 AND Col B is EITHER cell F4 OR cell G5 OR cell H6..

Cell I9 formula: Criteria Cell References which can be standardized, using SUMIF with MOD function: SUM col D where Col B is EITHER F8 OR F10. Array Formula (Ctrl+Shift+Enter).

Non-Contiguous Cell References for "Range" in SUMIF, using the volatile INDIRECT / OFFSET functions with SUMIF

Cell I13 formula: Uses named range "RngNonContg" (F12:F14) to search the 3 non-contiguous ranges "B2:B5", "B9" & "B14:B16" for "Car" criteria, and uses OFFSET with the named range for the SUM range ("D2:D5", "D9" & "D14:D16").

Cell I17 formula: Uses cell references (F16:F17) which contain the actual 2 non-contiguous ranges "B2:B8" & "B9:B11" to search for "Car" criteria, and uses OFFSET for the SUM range ("D2:D8" & "D9:D11").

Cell I19 formula: Uses cell references (F16:F17) which contain the actual 2 non-contiguous ranges "B2:B8 & B9:B11 to search for "Car" criteria, and uses cell references (G16:G17) for the SUM range ("D2:D8" & "D9:D11").

Cell I21 formula: Searches the 3 non-contiguous ranges "B2:B5", "B9", "B14:B16" for "Car" criteria & refers the corresponding SUM range ("D2:D5", "D9", "D14:D16"). Uses SUMIF with INDIRECT.

Cell I22 formula: Uses OFFSET to search the 3 non-contiguous ranges "B2:B5", "B9" & "B14:B16" for "Car" criteria, and also uses OFFSET for the SUM range ("D2:D5", "D9" & "D14:D16"). Uses SUMIF with OFFSET.

Using SUMIF with wildcard characters

Cell I29 formula: Uses wildcard Asterisk (*) which matches any sequence of characters: SUM col D for col C values ending with "land" where col B cells are non-blank.

Cell I30 formula: Uses wildcard Question mark (?) which matches any single character: SUM col D for col C values with any single character bewteen "I" & "eland" & non-blanks in col B.

Cell I32 formula: Uses wildcard Question mark (?) which matches any single character: SUM col D for col C values with any 3 characters before "land" & non-blanks in col B.

Cell I34 formula: Uses Tilde wild card ("~") which allows searching for words that contain a wild card (* or ?). Placing tilde before the asterisk tells Excel that asterisk should not be used as a wildcard. SUM col D for col C values which start with "*F" and where col B = "Metro".

Using SUMIF with Dates Range

Cell I39 formula: Uses SUMIF for Dates within a Month / Year - SUM Col D where Col A dates are in the Month & Year of cell I37 & and Col B cells are not blank.

Cell I42 formula: Uses SUMIF for Dates in Calendar Year - SUM Col D where Col A dates are in the Year (01 Jan-31 Dec) of cell I37 date & Col B cells are not blank.

Only four of the nineteen formulas are illustrated above. For full details and explanation refer to Ebook of Excel Formulas.