SUMIF function in Excel explained with examples

Calculating sum remains an important requirement in analysis in excel. Although doing simple sum is easy, many a times user need summation with criteria’s. For example sum is required for certain categories, sum is required at specific group level. This article explains SUMIF function in Excel explained with examples.

John works for a big retail giant. He has yearly spend data for his customers. Data also has information like gender, customer id, country etc. He wants to calculate total spend by Male and by Female separately in whole year. Below is the data example and expected results:

Below are the solution steps:

• Step 1: Selecting the Range and Criteria covers the grouping criteria. Range is the column on which criteria is tested. Here we are trying to sum Yearly Spend only if Gender is Female in row 5 & Male in row 6. Gender will be checked in column F. So Range is taken as F4:F29.

• Step 2: Next step is to fix the range. Pressing F4 Key can help in fixing the Range to $F$4:$F$29

• Step 3: Criteria would be the cell K5, i.e. the Gender for which we are aggregating spend.

• Step 4: Sum_Range would be the column which contains customer level Spend i.e. H4:H29.

• Step 5: Also we have to fix Sum_Range. Pressing F4 key can help in fixing the Sum_Range to $H$4:$H$29. Reason to fix the cells is we can drag the same formula down to L6.

• Step 6: Next step is to copy formula from cell L5 to L6. And here we have what John is looking for.

Problem 2: Another similar problem that John has is to calculate Total Yearly Spend for each country.

We will solve this one on similar approach.

• Step 1: Here the range will be the one where Country information is present i.e. G5:G29.

• Step 2: Next step is to fix the range. Pressing F4 Key can help in fixing the Range to $G$5:$F$294

• Step 3: Criteria would be the cell K5, i.e. the Country for which we are aggregating spend.

• Step 4: Sum_Range would be the column which contains customer level Spend i.e. H5:H29.

• Step 5: Again we have to fix Sum_Range. Pressing F4 key can help in fixing the Sum_Range to $H$5:$H$29. Reason to fix the cells is we can drag the same formula down to L6.

• Step 6: Then copy formula from cell L5 to L6:L8. Again John is able to achieve the desired results as well.

Hence, SUMIF can be a very handy function in Excel.

To get top certifications in Microsoft Excel and build your resume visit here. Also, you can read books listed here to build strong knowledge around Microsoft Excel. 

Visit our video lesson to learn more:

Looking to practice more with this example? Drop us a note, we will email you the Excel file: