Add up sales with SUMIFS

In this lesson

Example

How it works

SUMIFS adds rows that meet every condition you supply. Put the sum range first, then each criteria range and criterion. Start and end those ranges on the same rows so they describe the same records.

Use COUNTIFS when you need a number of records and AVERAGEIFS when you need an average. IF labels each row. Compare the result with this small list before using the formula on a larger report.

Steps

1. 1

   Enter Region, Rep and Sales in A1:C1, then these six records in rows 2–7: West/Maya/1200, East/Jordan/950, West/Priya/1800, North/Sam/700, West/Leo/1500 and East/Ana/1100.

2. 2

   Type West total in E1 and enter the formula below in F1.

   Excel argument separator

   Comma (,) Semicolon (;)

   =SUMIFS(C2:C7,A2:A7,"West")

   Copy code

   English function names. Formula settings →

3. 3

   Read the formula as: add C2:C7 where the matching A-row says West. Check 4500 by adding Maya 1200, Priya 1800 and Leo 1500.

4. 4

   Type East in E3 and North in E4. Enter the formula below in F3 and copy to F4. The $ signs keep both source ranges fixed while E3 changes to E4.

   Excel argument separator

   Comma (,) Semicolon (;)

   =SUMIFS($C$2:$C$7,$A$2:$A$7,E3)

   Copy code

   English function names. Formula settings →

5. 5

   Enter the two-condition formula below in a free cell. It includes only East sales over 1000, so it returns Ana’s 1100.

   Excel argument separator

   Comma (,) Semicolon (;)

   =SUMIFS(C2:C7,A2:A7,"East",C2:C7,">1000")

   Copy code

   English function names. Formula settings →

6. 6

   Filter Region to West and inspect the three Sales amounts. Compare their total with F1; the visible-only formula in the later step gives a clearer check than the status bar.

7. 7

   Type West count in H1 and enter =COUNTIFS(A2:A7,"West") in I1. Type West average in H2 and enter =AVERAGEIFS(C2:C7,A2:A7,"West") in I2. Check 3 records and average 1500.

8. 8

   Type DealSize in D1, enter =IF(C2>=1500,"Large","Standard") in D2 and fill down. Only Priya and Leo should be Large. In a free cell, try =TRIM(" West ") to remove the surrounding spaces from the region text.

9. 9

   With West filtered, enter =SUBTOTAL(109,C2:C7) in a free cell and check 4500. This sums the visible rows. Clear the filter when you finish.

Try it yourself

1. In F6, total West sales of at least 1500 using two conditions.
2. Use COUNTIFS to count West sales of at least 1500, then explain why counting those deals produces 2 while summing them produces 3300.

If something goes wrong

SUMIFS returns zero for a region you can see.

Check the region text for extra spaces and the Sales cells for text amounts. Clean a copy with TRIM and test ISNUMBER on the amounts before accepting zero.

The copied summary uses different source rows.

Use the supplied $C$2:$C$7 and $A$2:$A$7 ranges. Inspect the first and last copied formulas: only the criterion cell should change.

Quick check

Try these questions after the exercise. Check the result in your app before marking the lesson complete.

More from Microsoft

These Microsoft guides cover the tools used in this lesson.

• SUMIFS function ↗