Excel essentials every analyst uses: SUMIFS, COUNTIFS, IF, IFS and nested logic

Most analyst work in Excel comes down to two questions. How much, or how many, of something meets a set of conditions? And what should happen to a row when a condition is true? SUMIFS and COUNTIFS answer the first. IF, IFS and their nested cousins answer the second.

None of these functions is new or clever, which is the point. Learn them properly and you can answer most ad hoc questions from a sales export, a CRM dump or a finance ledger in a few minutes, without building a PivotTable or asking someone else to run a report.

SUMIFS: add up only the rows that match

SUMIFS adds the numbers in one column where other columns meet your conditions. The syntax is:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

The column you want to add comes first. After it come pairs of “where to look” and “what to look for”. Microsoft allows up to 127 range and criteria pairs, which is far more than you will ever need. A few rules matter in practice:

• Every criteria range must be the same size and shape as the sum range. Mixing C2:C500 with A2:A499 will not work.
• Text criteria go in quotation marks, for example "North". Microsoft notes that a result of zero often means the quotes are missing.
• Comparison operators also go inside quotes: ">=1000", "<>Cancelled".
• Wildcards work: ? matches one character and * matches any run of characters. Put a tilde in front (~*) to match a real asterisk.

One trap catches people who learned the older SUMIF first. SUMIF puts the range to add at the end, while SUMIFS puts it at the start. Microsoft flags this as a common source of errors when people copy formulas between the two. Many analysts use SUMIFS even for a single condition so the argument order never changes.

COUNTIFS: count the rows that match

COUNTIFS works the same way but counts rows instead of adding values:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

The logic is AND. Excel checks each row across all the ranges, and a row is counted only if every condition is true for it. The same size rule applies, though the ranges do not have to sit next to each other. One quirk is worth remembering: if a criterion points at an empty cell, COUNTIFS treats that cell as zero. If you drive criteria from input cells, a blank input will silently change your answer.

COUNTIFS on its own also gives you a quick duplicate check. =COUNTIFS(A:A, A2) next to a list of order IDs returns 2 or more wherever an ID repeats.

IF: make a decision for each row

IF returns one value when a test is true and another when it is false: =IF(test, value_if_true, value_if_false). Analysts use it to flag rows (=IF(D2>=1000,"Large","Small")), to avoid errors (=IF(C2=0,"",B2/C2)) and to build helper columns that a later SUMIFS can use.

You can combine IF with AND and OR to test several things at once. =IF(AND(B2="UK",D2>500),"Priority","") flags UK orders over 500. Keep these short. If a single IF needs more than two or three conditions, the logic probably belongs in a helper column or a lookup table.

Nested IFs and why IFS is usually better

A nested IF puts one IF inside another to handle more than two outcomes. Excel allows up to 64 levels, but Microsoft’s own guidance on nested IF formulas says it is “not at all advisable” to go that far. Its reasons are practical: long nests are hard to build correctly, they can return wrong answers only some of the time, and the next person to open the file will struggle to maintain them.

Order also matters. Excel stops at the first true test. In Microsoft’s commission example, a formula that checks the lowest threshold first gives a large sale the lowest rate, because the first test is already true. Always test from the most restrictive condition to the least, or from highest to lowest.

IFS replaces a chain of nested IFs with one function that takes test and result pairs in order:

=IFS(D2>89,"A",D2>79,"B",D2>69,"C",TRUE,"Below C")

Two details make IFS safe to use. If no test is true, IFS returns #N/A, so add TRUE as the last test to set a default. And IFS is only available in Excel for Microsoft 365, Excel 2024, Excel 2021 and Excel 2019. If colleagues use older versions, they will see an error.

When the list of thresholds is likely to change, Microsoft suggests a different route: put the thresholds and results in a small sorted table and look them up. Changing a rate then means editing a cell, not a formula.

A worked example (illustration only)

A small online shop exports its orders to a sheet with these columns: Order date (A), Channel (B), Region (C), Status (D) and Net value (E). The founder wants three numbers for the monthly review.

Notice the date criteria. Joining an operator to a date with & lets you set a start and an end, and using “less than the first of next month” avoids missing orders on the last day. In a real file, put the channel, region and dates in input cells and point the formulas at those cells, so the same sheet answers next month’s questions too.

Common mistakes

• Ranges of different sizes. Use whole columns or an Excel table so every range lines up.
• Numbers stored as text. A value such as “100” held as text can be skipped by a numeric criterion like ">=100", so convert it first.
• Stray spaces. “North ” with a trailing space does not match “North”. Clean the data before you trust the totals.
• Hard-coded criteria. Typing “March” into twenty formulas means twenty edits next month.
• Nests nobody can read. If you need Alt+Enter line breaks to follow your own formula, move to IFS or a lookup table.
• No default in IFS. Without a final TRUE, unexpected values return #N/A.

When to use these functions, and when not to

Use SUMIFS and COUNTIFS when you need a few specific numbers in a fixed layout, such as a monthly summary or a KPI table that must look the same every time. Use IF and IFS for row-level labels and flags that feed those summaries.

They are a poor fit when you are still exploring and do not yet know which cuts matter. A PivotTable is faster for that. They also struggle when the logic is really a mapping, such as product code to category, where a lookup table is cleaner. And if you are combining several files every week, clean and join the data first, then summarise it with these functions at the end.