SUM Cell values containing any of the Multiple Values in Full or in Part
SUMIF – SUM corresponding column B cells where column A cells contain any of the Multiple Values (C2:C5) in Full or in Part.
Cell E8 formula: Return SUM of corresponding column B cells, where column A cells contain any of the multiple values (C2:C5), in full or in part.
———————————————————————————————–
Cell E8 formula: Return SUM of corresponding column B cells, where column A cells contain any of the multiple values (C2:C5), in full or in part.
This formula considers column B cell value multiple times for each cell C2:C5 value appearing in a column A cell. Ex. if a column A cell contains more than one of the C2:C5 values viz. if say both C2 & C3 values are contained in cell A2, then corresponding cell B2 value is considered twice. However, if cell A2 contains cell C2 value twice, corresponding cell B2 value is considered only once.
Uses SUMIF with cell reference of C2:C5. Case-insensitive search (ex. considers both “White” & “white”).
Cell E8 formula:
=SUM(SUMIF( A2:A20, IF(C2:C5<>””, “*”&C2:C5&”*”, “”), B2:B20))
Explanation – Cell E8 formula (Array formula – Ctrl+Shift+Enter):
1) IF(C2:C5<>””, “*”&C2:C5&”*”, “”): returns an array of values – {“*Top*”; “*white*”; “*Blue*”;””} – which is the criteria argument of the SUMIF function. Using the IF condition returns “” for the blank cells in the criteria range C2:C5. This enables the formula to use blank cells in the criteria range of C2:C5 so that only the cells with values are considered as criteria.
2) SUMIF(A2:A20, IF(C2:C5<>””, “*”&C2:C5&”*”, “”), B2:B20): returns an array of numbers – {137; 140; 58;0} – the numbers represent the SUM of column B cells where corresponding column A cells contain either of the 4 specified values in whole or in part. The criteria argument of the SUMIF function – {“*Top*”; “*white*”; “*Blue*”;””} – uses the asterik (*) wildcard which indicates that there could be any number of characters before and after each criteria text which means that the text “Skyblue” will be considered for containing “Blue” and the text “Topper” will be considered for containing “Top”. For example, the 1st number of 137 is the sum of column B cells where column A cells contain “Top” in full or in part – cells B2, B3, B4, B6, B9 and B10 are considered and their respective values (10+ 20+12+24 +45+26) are added to return the sum of 137. Similarly the next number 140 represents the sum of column B cells where column A cells contain “white” in full or in part. Using SUM returns the Sum of all 4 criteria cells.
—————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Other formula(s):
Cell E7 formula: Return SUM of corresponding column B cells, where column A cells contain any of the multiple values (C2:C5), in full or in part. This is similar to cell E8 formula illustrated above, except that this formula uses absolute reference for the 3 values “Top”, “white” and “Blue”. It considers column B cell value multiple times for each cell C2:C5 value appearing in a column A cell. Ex. if a column A cell contains more than one of the C2:C5 values viz. if say both C2 & C3 values are contained in cell A2, then corresponding cell B2 value is considered twice. However, if cell A2 contains cell C2 value twice, corresponding cell B2 value is considered only once. Case-insensitive search (ex. considers both “White” & “white”). Normal, Non-Array formula.
Cell E13:E14 formulas: Return SUM of corresponding column B cells, where column A cells contain any of the multiple values (C2:C5), in full or in part. These consider column B cell value only once even if more than one of the cell C2:C5 values appear in a column A cell. Ex. if a column A cell contains more than one of the C2:C5 values viz. if say both C2 & C3 values are contained in cell A2, then corresponding cell B2 value is considered ONLY ONCE. Also, if cell A2 contains cell C2 value twice, corresponding cell B2 value is considered only once. Cell E13 formula does a Case-Insensitive search (ex. considers both “White” & “white”) and it is an Array formula (Ctrl+Shift+Enter). Cell E14 formula does a Case-Sensitive search (ex. considers only “white” & not “White”) and it is an Array formula (Ctrl+Shift+Enter).