The SUMIF formula in Excel adds cells within a range from a given condition or criteria. The SUMIFS formula works in the same way yet can support multiple conditions and criteria. Given my data set of baseball games between the Reds and Astros in 2010, I would like to answer how many were in attendance at Great American Ball Park. I’ll use a SUMIF to do so. I will use a SUMIFS formula to answer how many were in attendance at Minute Maid Park on Fridays.
The SUMIF has 3 arguments. The first one is Range. In this example I will use the cells containing the stadium field. The Criteria is “Great American Ball Park” and the Sum_Range is the attendance field.
See the color blue shaded cells below for a visual representation of what rows the formula is working with.
Now on to the SUMIFS. Use this one when you have a set of multiple conditions or criteria.
The SUMIFS formula’s first argument is the Sum_Range. This will be the range where the actual cells are being added. Then you can add a team of Criteria_range1 with Criteria1 that follows Criteria_range2 with Criteria2. See below for my example of using “Fri” and “Minute Maid Park”.
Below is a color shading of the actual cells that Excel is adding up.
Want to practice with my training file? Download Sumif and Sumifs.xlsx.