Print

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

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.

    

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

 

Sumifs - Non-Contiguous Cells, Wildcards, Date Range  

 

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.