What Does Excel Sumif and Sumifs Do?

Excel’s Sumif and Sumifs allows you to query your tabular data for numerical answers to your questions like what was our sales or costs for January last year?

Or in the case of this example here what was the total volume traded on the NYSE for ticker "abev"?

The YouTube video above takes you through a practical example of sumif and sumifs using the tabular data below.

You can get the spreadsheet and all future giveaways when you opt into the yellow newsletter box further down this page.

Sumif takes three arguments, the column range that contains the criteria (in this case column "F"), the criteria ("abev") and finally the range to sum, which for this example is the "Volume" column (Column "D").

In this example I have chosen the ranges to extend from row 4 to row 65000 to allow for expansion of the data range.

There are however more clever ways to do this using the counta function to create a dynamic range which is explained in the youtube tutorial on this page.

What if you have more than one criteria?

When you need to query on more than one criteria this is where sumifs (i.e. sumif with an "s" at the end) steps in and takes over.

Sumifs accepts a minimum of three arguments when you have one set of criteria, don’t get caught out here because the order of the arguments change for sumifs compared to sumif.

Instead of starting off with the criteria range, the first argument is now the range to be summed (i.e. the "Volume") column, followed by two arguments the criteria range and the criteria.

You can continue to add extra arguments in pairs – criteria range and criteria, this example is using sumifs to find the total volume traded on the NYSE for security "abev" on wednesdays.

The great thing about sumif, sumifs and the dsum functions are that you can easily verify the results of the function by performing an autofilter on the data and summing up your value column using the subtotal column.

In the above instance the sumif function is filtered on "abev", "nyse" and "Wednesday" and gives the value 87,509,400 in Cell B7.

This result of 87,509,400 can be verified with the autofilter by selecting the same criteria in the autofilter as used by the SumIfs function, the subtotal function in cell D1 of the data sheet above confirms the results of the SumIfs function.

This can be useful if you need to "sample test" some of your numbers.

The attached youtube tutorial which lasts about 15 minutes goes into all of the above and more in detail.