SUM AND / OR Criteria for Multiple Columns
SUMIF – Either / Or for 2 Columns separately – if EITHER Column A OR Column B contain respective values, then return SUM of Column C.
Cell H6 formula: If Either Column A contains a specific value, AND / OR Column B contains a specific value, return SUM of corresponding Column C values.
———————————————————————————————–
SUMIF Either of Multiple Columns Condition
Cell H6 formula: If Either G2 value (“mars”) in Column A, AND / OR G3 value (“venus”) in Column B, return SUM of corresponding values from Column C.
Note: Even if both conditions are satisfied (ie. G2 in column A AND G3 in column B), corresponding column C value is considered only once.
Uses the FREQUENCY function and considers columns A & B (& criteria cells G2 / G3) in a single range as a combo instead of individually. This formula is ideal to be used for multiple columns or multiple criteria cells and does not consider each column or criteria cell separately which may make the formula unwieldy and complex.
Cell H6 formula:
=SUM(IF( FREQUENCY( IFERROR( IF(MATCH( A2:B20, G2:G3, 0)= TRANSPOSE( ROW(G2:G3)- ROW(G2)+1), ROW(A2:A20)- ROW(A2)+1), “”), ROW(A2:A20)- ROW(A2) +1), C2:C20))
Explanation – Cell H6 formula (Array formula – Ctrl+Shift+Enter):
1) MATCH(A2:B20, G2:G3,0): returns an array of 1s, 2s and error values, covering 2 columns for each of the 19 rows – {1,1; #N/A,#N/A; 1,2; #N/A,2; 1,#N/A; #N/A,1; 2,#N/A; 2,1; 1,2; 2,1; 2,2; 2,#N/A; 1,2; #N/A,1; #N/A, #N/A;…} – 1s indicate that the cell = G2, 2s indicate that cell = G3. For ex. the first “1,1” are for the 1st row cells where A2 = G2, cell B2 = G2; the next “#N/A, #N/A” are for 2nd row cells and indicate that neither of the cells A3 or B3 contain either G2 or G3; the next “1,2” are for the 3rd row cells and indicate that cell A4 = G2 and cell B4 = G3; and so on.
2) TRANSPOSE( ROW(G2:G3)- ROW(G2)+1): returns {1,2}. This is used to match the above array values of 2 columns in each row, because the requirement is that column A should equate to 1 and column B should equate to 2.
3) IFERROR(IF( MATCH( A2:B20, G2:G3,0)= TRANSPOSE( ROW(G2:G3)- ROW(G2)+1), ROW(A2:A20)- ROW(A2)+1), “”): returns an array of numbers, FALSE values and blanks, comprising of 2 columsn and 19 rows – {1,FALSE; “”,””; 3,3; “”,4; 5,””; “”,FALSE; FALSE,””; FALSE,FALSE; 9,9; FALSE, FALSE; FALSE,11; FALSE,””; 13,13; “”,FALSE; “”,””; “”,””; “”,””; “”,””; “”,””} – this equates the above 1) & 2) and returns an array of row numbers (2 columns for each row number) for matched values and returns blanks for error values. For example, the 1st row of “1,FALSE” indicates that cell A2 = G2 because its in column A and FALSE is for cell B2 which has cell G2 value but does not match cell B3 value; the next 2 blanks are for 2nd row which eman that neither cell A3 or B3 contain G2 / G3 values; the next “3,3”s means that cell A4 = G2 and B4 = G3; the next “””,4″ means that cell A5 is neither G2/ G3 and cell B5 = G3;, and so on. Note: the row numbers are returned only if column A cells = G2 and column B cells = G3 and row numbers are not returned even if column A cells + G3 or column B cells = G2 so that a row number will appear once if col A = G2 OR col B = G3, or it will appear twice both Col A = G2 AND col B = G3. This is the data_array argument of the FREQUENCY function.
4) ROW(A2:A20)- ROW(A2)+1: This returns an array of values 1 to 19 – {1;2;3;4; 5;6; 7;8;9; 10; 11;12; 13;14;15; 16;17; 18;19} – This is the bins_array argument of the FREQUENCY function against which the data_array values are grouped to determine their occurrence and frequency.
5) FREQUENCY( IFERROR( IF(MATCH( A2:B20, G2:G3,0)= TRANSPOSE( ROW(G2:G3)- ROW(G2)+1), ROW(A2:A20)- ROW(A2)+1), “”), ROW(A2:A20)- ROW(A2)+1): returns an array of numbers and zeros – {1;0; 2;1; 1;0; 0;0; 2;0; 1;0; 2;0;0;0; 0;0; 0;0} – using the FREQUENCY function with the data_array argument in 3) above, and the bins_array argument in 4) above, returns the number of times each row number in bins_array argument occurs (this is the frequency of occurrence). Note that only the 1st instance of each number of bins_array returns the frequency. This means that frequency is returned for each unique number of bins_array argument. Using the FREQUENCY function returns the number of cells in each row where column A = G2 and column B = G3 so that if either column A = G2 or column B = G3 then 1 is returned, if both column A cell = G2 and column B cell = G3 then 2 is returned, and so on. Example, the 1st number 1 means that cell A2 = G2 and cell B2 <> G3; the next zero means that cell A3 <> G2 and B3 <> G3; the next number 2 means that cell A4 = G2 and cell B4 = G3; and so on.
6) IF(FREQUENCY( IFERROR(IF( MATCH(A2:B20, G2:G3,0)= TRANSPOSE( ROW(G2:G3)- ROW(G2)+1), ROW(A2:A20)- ROW(A2)+1),””), ROW(A2:A20)- ROW(A2)+1), C2:C20): this returns an array of numbers and FALSE values – {8; FALSE;12; 14; 6;FALSE; FALSE; FALSE; 9;FALSE;1; FALSE;14; FALSE;…} – where the Frequency in 5) above is 1 or more the corresponding column C values are returned. The values are returned if (i) col A = G2 OR col B = G3, or (ii) both Col A = G2 AND col B = G3). Using SUM returns the total of these column C values.
—————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Other formula(s):
Cell H5 formula: Gives same result as the above cell H6 formula. If Either G2 value (“mars”) in Column A, AND / OR G3 value (“venus”) in Column B, return SUM of corresponding values from Column C. Note: Even if both conditions are satisfied (ie. G2 in column A AND G3 in column B), corresponding column C value is considered only once. Uses SUMPRODUCT, though a simple formula it considers each column (A & B) separately – not ideal where number of columns are large – Normal Non-Array formula.
Cell H9 formula: If Either G2 value (“mars”) in Column A, OR G3 value (“venus”) in Column B, but NOT BOTH, return SUM of corresponding values from Column C. Uses SUMPRODUCT, though a simple formula it considers each column (A & B) separately – not ideal where number of columns are large – Normal Non-Array formula.
Cell H10 formula: Gives same result as the above cell H9 formula. If Either G2 value (“mars”) in Column A, OR G3 value (“venus”) in Column B, but NOT BOTH, return SUM of corresponding values from Column C. Uses the FREQUENCY function, and considers columns A & B (& criteria cells G2 / G3) in a single range as a combo instead of individually. This formula is ideal to be used for multiple columns or multiple criteria cells and does not consider each column or criteria cell separately which may make the formula unwieldy and complex. Array formula (Ctrl+Shift+Enter).
Cell H13 formula: If G2 value (“mars”) in Col A, AND G3 value (“venus”) in Col B, return SUM of corresponding values from Col C – BOTH values required in respective columns A / B. Uses SUMPRODUCT, though a simple formula it considers each column (A & B) separately – not ideal where number of columns are large – Normal Non-Array formula.
Cell H14 formula: Gives same result as the above cell H13 formula. If G2 value (“mars”) in Col A, AND G3 value (“venus”) in Col B, return SUM of corresponding values from Col C – BOTH values required in respective columns A / B. Uses the FREQUENCY function, and considers columns A & B (& criteria cells G2 / G3) in a single range as a combo instead of individually. This formula is ideal to be used for multiple columns or multiple criteria cells and does not consider each column or criteria cell separately which may make the formula unwieldy and complex. Array formula (Ctrl+ Shift+ Enter).