The Sumifs function

The spreadsheet below shows the quarterly sales figures for 3 sales representatives.

The Sumifs function can be used to find total sales figures for any combination of quarter, area and sales rep.

This is shown in the examples below.

ABCD
1QuarterAreaSales Rep.Sales
21NorthJeff$223,000
31NorthChris$125,000
41SouthCarol$456,000
52NorthJeff$322,000
62NorthChris$340,000
72SouthCarol$198,000
83NorthJeff$310,000
93NorthChris$250,000
103SouthCarol$460,000
114NorthJeff$261,000
124NorthChris$389,000
134SouthCarol$305,000
Example 1

To find the sum of sales in the North area during quarter 1:

=SUMIFS( D2:D13, A2:A13, 1, B2:B13, "North" )

which gives the result $348,000.

In this example, the Excel Sumifs function identifies rows where:

The value in column A is equal to 1
and
The entry in column B is equal to "North"

and calculates the sum of the corresponding values from column D.

ie. this formula finds the sum of the values $223,000 and $125,000 (from cells D2 and D3).

Example 2

Again, using the data spreadsheet above, we can also use the Sumifs function to find the total sales for "Jeff", during quarters 3 and 4:

=SUMIFS( D2:D13, A2:A13, ">2", C2:C13, "Jeff" )

This formula returns the result $571,000.

In this example, the Excel Sumifs function identifies rows where:

The value in column A is greater than 2
and
The entry in column C is equal to "Jeff"

and calculates the sum of the corresponding values in column D.

ie. this formula finds the sum of the values $310,000 and $261,000 (from cells D8 and D11).