Now that you’re familiar with the comparison of data using the pivot table, let’s look at the SUMIFS function in Excel and how it can be used to compare year-over-year data, and better present and analyse your data.

SUMIFS is a function used to sum cells that meet multiple criteria and can be used to sum values when adjacent cells meet criteria based on dates, numbers and text.

The SUMIFS syntax relies on the sum and criteria ranges:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Some important information to remember:

• The sum range is the range that you want to summarise. This would typically be a table or your pivot table.
• You can reference row and column headings to indicate your criteria ranges.
• You can use relative, absolute and mixed references in your syntax.
• You can reference the filter of your pivot table and change between values in your filter.
• You can reference ‘All’ values, a special value used in the syntax, or a specific value.

For more information on the SUMIFS function, read through the following additional resources:

• SUMIFS function

• Switching between relative, absolute, and mixed references

Join the discussion

To SUMIF or to SUMIFS? Let us know your thoughts on the difference between the SUMIF and SUMIFS functions in Excel. Do you have any tips and tricks for using these functions that you’d like to share?

Use the Discussion section below and let us know your thoughts. Try to respond to at least one other post and once you’re happy with your contribution, click the Mark as Complete button to move on to the next step.