Skip main navigation

New offer! Get 30% off one whole year of Unlimited learning. Subscribe for just £249.99 £174.99. New subscribers only. T&Cs apply

Find out more

Comparing Year-Over-Year Values Using the SUMIFS Function


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:

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.
This article is from the free online

How to Use Microsoft Excel for Data Analysis

Created by
FutureLearn - Learning For Life

Reach your personal and professional goals

Unlock access to hundreds of expert online courses and degrees from top universities and educators to gain accredited qualifications and professional CV-building certificates.

Join over 18 million learners to launch, switch or build upon your career, all at your own pace, across a wide range of topic areas.

Start Learning now