SUMIF vs SUMIFS vs COUNTIFS: which Excel function to use when

Excel has a whole family of "add this up, but only for some rows" functions, and their names look almost identical. The rule that sorts them out is short: IF means one condition, IFS means several. The rest is remembering which argument goes first.

TL;DR: one condition → SUMIF / COUNTIF / AVERAGEIF; more than one → SUMIFS / COUNTIFS; just count non-empty cells → COUNTA.

The examples below use a staff sheet with 25 people in rows 2 to 26: column A is the name, B the department, C the office, D the sales figure. We ran each formula on that sheet in Excel for the web.

The functions, one by one

1. SUMIF: add with one condition.
   =SUMIF(B2:B26,"Sales",D2:D26) adds column D only where the department is "Sales". Order: where to check, what to match, what to add.

2. SUMIFS: add with several conditions.
   =SUMIFS(D2:D26,B2:B26,"Sales",C2:C26,"Seoul") adds sales for people who are in Sales and in the Seoul office. Note that the range to add (D) comes first here, the opposite of SUMIF. This is the most common source of wrong results.

3. Writing conditions. Number conditions also go inside quotes: =COUNTIF(D2:D26,">=200") counts people with sales of 200 or more. Text conditions are quoted too, as in "Sales".

4. COUNTA: how many entries.
   =COUNTA(A2:A26) counts non-empty cells, so it tells you how many people are on the list.

5. COUNTIF: count with one condition.
   =COUNTIF(B2:B26,"Sales") gives the head count of the Sales department.

6. COUNTIFS: count with several conditions.
   =COUNTIFS(B2:B26,"Sales",C2:C26,"Seoul") counts people in Sales who are also in Seoul.

7. AVERAGEIF: average with one condition.
   =AVERAGEIF(B2:B26,"Sales",D2:D26) returns the average sales for the Sales department.

Mistakes worth checking for

• Ranges of different sizes. In the IFS functions, B2:B26 paired with D2:D25 gives an error. Keep every range the same height.
• Typing the condition as a bare comparison. >=200 without quotes won't work. If the threshold lives in a cell, join it: ">="&F1.
• Extra spaces in the data. "Sales " with a trailing space does not match "Sales". If counts look low, clean the column with TRIM first.
• Using COUNT instead of COUNTA. COUNT only counts numbers, so a column of names returns 0.

The pandas equivalent

If the same sheet ends up in a pandas DataFrame, the logic maps directly, and multiple conditions read a little more naturally:

mask = (df["dept"] == "Sales") & (df["office"] == "Seoul")
df.loc[mask, "sales"].sum()      # SUMIFS
mask.sum()                       # COUNTIFS

In the workbook itself, though, the formulas recalculate as soon as someone edits a row, which is usually what a shared report needs.

---

AI-assisted writing. Every example formula was run on a sample sheet in Excel for the web.