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.
———————————————————————————————–
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.
—————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
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.