SUMIF with Date Range, Multiple Month Names / Numbers & Year
Return SUM of values corresponding to One or Multiple Months, Date Range, and Multiple Names – using SUM, SUMIFS or SUMRODUCT.
Cell F6 formula: Return SUM of column C values, where column A month numbers are either 4, 1, or 3 (E4:E6), & column B = cell D4 (“Smith”).
———————————————————————————————–
Cell F6 formula: Return SUM of column C values, where column A month numbers are either 4, 1, or 3 (E4:E6), & column B = cell D4 (“Smith”).
Considers Cell References (E4:E6) for Month Numbers to equate with column A month numbers, uses SUM with IF.
Cell F6 formula:
=SUM(IF(( MONTH( A2:A20)= TRANSPOSE( E4:E6))*( B2:B20=D4)*( A2:A20<>””), (C2:C20)))
Explanation – Cell F6 formula (Array formula – Ctrl+Shift+Enter):
1) TRANSPOSE(E4:E6): returns an array of values from E4:E6 cells – {4,1,3}. TRANSPOSE function is used to convert “{4;1;3}”, which is returned by using “E4:E6”, to a column-separated array of “{4,1,3}” to make it amenable to be used in the formula. One of the array constants should be a single-column array, while the other should be a single-row array. In Excel, commas represent column-separators and semi-colons represent row-separators.
2) (MONTH( A2:A20)= TRANSPOSE(E4:E6)): returns an array of TRUE and FALSE values, 3 columns (for each of the 3 months) for each of the 19 rows in A2:A20 – {FALSE,FALSE,TRUE; TRUE, FALSE, FALSE; FALSE, TRUE, FALSE; FALSE, FALSE,TRUE; TRUE, FALSE, FALSE;…} – where TRUE indicates a column A date whose month number is same as one of the 3 months. Each of the 19 rows have 3 columns each, the 1st column equates with 4, the 2nd column with 1 and the 3rd column with 3 ex. the 1st row has “FALSE, FALSE, TRUE;” where FALSE is for month numbers 4 & 1 and TRUE is for month number 3 because cell A2 date equates with March (ie. 3).
3) It is necessary to include “(A2:A20<>””)” in the formula to exclude blank column A cells, because blank cells imitate month number 1 & return TRUE so that using “MONTH(A2:A20)” will return 1 for blank cells, and hence if a column A cell is blank (ex. A15) then its corresponding column C value will also get considered. “(B2:B20=D4)” is used to consider only those column C values where column B contains the cell D4 name.
4) (MONTH( A2:A20)= TRANSPOSE( E4:E6))*( B2:B20=D4)*( A2:A20<>””): the formula tests the 3 conditions that the month numbers of column A dates are either 4, 1 or 3, and column A cells are not blank and column B = cell D4, which returns an array of 1s and zeros comprising of 3 columns and 19 rows – {0,0,1; 0,0,0; 0,1,0; 0,0,0; 1,0,0; 0,0,0; 0,1,0; 0, 0,0;…} – where 1s indicate where ALL the 3 conditions are satisfied, for ex. the 1st row of “0,0,1” means that cell A2 is not blank and its month number is 3 and cell B2 = cell D4 value. These 1s are multiplied with column C values “(C2:C20)” – note that there can be only one 1 in a row (because cell A2 month can be equal to only one of the 3 month numbers). Cell F5 / F6 formulas use the IF statement to return column C values where 1 is returned. Using SUM returns the Total of the relevant column C values.
—————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Other formula(s):
Cell F4:F5 formulas: Gives same result as the above cell F6 formula. Return SUM of column C values, where column A month numbers are either 4, 1, or 3 (E4:E6), & column B = cell D4 (“Smith”). Both Consider Absolute Values ie. {4,1,3}, for Month Numbers to equate with column A month numbers. Cell F4 formula uses SUMPRODUCT and is a Normal, Non-Array formula whereas cell F5 formula uses SUM with IF and is an Array formula (Ctrl+Shift+Enter).
Cell F9:F10 formulas: Return SUM of column C values, where column A month names are either of cells E9:E11, & column B = cell D4. Both formulas use cell references for criteria match. Cell F9 formula uses TEXT function to equate column A Month Names with E9:E11, uses SUM with IF – Array formula (Ctrl+Shift+Enter). Cell F10 formula Converts E9:E11 Month Names to numbers to equate with column A Month numbers, uses SUM function – Array formula (Ctrl+Shift+Enter).
Cell F14 formula: Return SUM of column C values, where column A month number is either 4, 1, or 3 (E4:E6), column A year = cell E14, & column B = cell D4 (“Smith”). Uses SUM, & TEXT function to validate column A month / year, & cell references for the criteria. Array formula – Ctrl+ Shift+ Enter.
Cell F17:F19 formulas: Return SUM of column C values, where column A dates are within the E17 date’s month & year, & column B = cell D4 (“Smith”). All are Normal, Non-Array formulas. Cell F17 formula uses SUMIFS / EOMONTH to validate column A dates within cell E17 date range, & cell reference for the name criteria. Cell F18 formula uses SUMPRODUCT, & TEXT function to validate column A dates within cell E17 date range, & cell reference for the name criteria. Cell F19 formula uses SUMPRODUCT, & converts column A dates / E17 date, to the 1st of the respective month(s) to validate, & cell reference for the name criteria.
Cell F22:F24 formulas: Return SUM of column C values, where column A dates are within cell E22 date’s month & year, & column B = either cell D22 OR cell D23 (“Smith” or “Jones”). Cell F22 formula Uses SUMIFS / EOMONTH to validate column A dates within cell E22 date range, & absolute values for the names criteria – Normal, Non-Array formula. Cell F23 formula uses SUMIFS / EOMONTH to validate column A dates within cell E22 date range, & cell reference for the names criteria – Normal, Non-Array formula. Cell F24 formula uses SUM, & TEXT function to validate column A dates within cell E22 date range, & cell reference for the names criteria. Array formula – Ctrl+Shift+Enter.
Cell F27 formula: Return SUM of column C values, where column A dates are within Start Date of cell E27 & End Date of E28, & column B = cell D27 (“Jones”). Uses SUMIFS to determine if column A dates are within the Start – End Dates of cells E27 – E28, & column B = cell D27 – Normal, Non-Array formula.