Using the SUMIF Function with Multiple Criteria in Different Columns in Excel – 3 Methods

Formula Breakdown:

• SUMIF($B$5:$B$16,H4,E5:E16): goes through B5:B16 to look for the value in H6 (T-shirt). It sums all values in E5:E16 and returns the sum.

Formula Breakdown:

• SUMIF($B$5:$B$16,H5,$E$5:$E$16): returns the sum of the revenues associated with the value in H5 (Shirt).
• SUMIF($B$5:$B$16,I5,$E$5:$E$16): returns the sum of values associated with Pants in E5:E16.
• SUMIF($B$5:$B$16,H5,$E$5:$E$16)+SUMIF($B$5:$B$16,I5,$E$5:$E$16): sums the values returned by the previous expressions.

Formula Breakdown:

• SUMIF($B$5:$B$16,{“Shirt”,”Pants”},F5:F16): goes through B5:B16 to look for Shirts and Pants. It sums the profit of these products in F5:F16 and returns it as the input for the SUM function.
• SUM(SUMIF($B$5:$B$16,{“Shirt”,”Pants”},F5:F16)): returns the sum of the profit.

Formula Breakdown:

• SUMIF(B5:B16,H5:I5,F5:F16): The values in H5:I5 are the criteria. The function goes through B5:B16 to look for the criteria and sum the values individually. It returns the values as arguments for the SUM function.
• SUM(SUMIF(B5:B16,H5:I5,F5:F16)): sums the values returned by the SUMIF function.

Formula Breakdown:

• SUMIFS($E$5:$E$16,$B$5:$B$16,H5,$C$5:$C$16,I5): The first argument, $E$5:$E$16, is the sum range of the function (revenue). The second argument, $B$5:$B$16, is the criteria range for the first criterion (Shirt in H5). The last two arguments are the second criteria range and the second criterion. The function looks for shirts in the first criteria range and white in the second. It returns the total revenue of white shirts.