Excel IFS vs COUNTIF: When to Use Each

Formulas & functions · Updated November 18, 2026

In Excel, IFS tests conditions in order and returns a corresponding result, while COUNTIF counts cells that meet one condition. They are different functions, but you can combine them—for example, count overdue items with COUNTIF, then use IFS to label the result.

What “IFS COUNTIF” usually means

People searching for “Excel IFS COUNTIF” are often trying to decide which function to use, or how to put both into one formula. The short answer is that the functions solve different parts of a problem. Use COUNTIF when the result you need is a number of matching cells. Use IFS when you need Excel to choose a result based on which condition is true.

For example, a status report might need to count how many rows say “Overdue.” That is a counting task. A separate cell might need to show “Urgent,” “Follow up,” or “Clear” depending on one or more counts. That is a decision task. A formula can combine the two: COUNTIF supplies a count that IFS evaluates.

They are not interchangeable names for the same operation. IFS does not count matching cells, and COUNTIF does not select among several return labels based on a sequence of tests.

How the IFS function works

IFS checks logical tests from left to right. When it finds the first test that is true, it returns the value paired with that test and stops checking. Its arguments therefore come in test-and-result pairs: a condition, the value to return if that condition is true, another condition, and its result.

Suppose a student’s score is in B2. With 90 as the A threshold, 80 as the B threshold, and 70 as the C threshold, this formula assigns a grade. For a score of 84 in B2, it returns “B.”

With the score in B2, enter: =IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"Below C").

Order matters. A test for 70 or more placed before the test for 90 or more would capture a score of 95 first, so Excel would return “C” without reaching the A test. Put the most specific or highest-priority conditions first. The final TRUE acts as a catch-all, giving the formula a result when none of the earlier conditions matches. Without such a final condition, a value that passes none of the tests returns #N/A.

How the COUNTIF function works

COUNTIF checks one range against one criterion and returns the number of matching cells. Its arguments are the range to inspect and the criterion to apply. The criterion can be a literal value, a text string, an expression using comparison operators, or a reference to a cell containing the criterion.

For example, suppose A2:A8 contains these statuses: Paid, Pending, Paid, Overdue, Pending, Paid, Overdue. To count the three cells containing “Paid,” use the following formula.

With statuses in A2:A8, enter: =COUNTIF(A2:A8,"Paid"). The result is 3.

For a comparison such as “at least 100,” place the operator and number together inside the criterion string. With amounts in C2:C20, this formula counts values that are 100 or greater.

With amounts in C2:C20, enter: =COUNTIF(C2:C20,">=100").

If the criterion is stored in a cell, you can join an operator to that cell using the ampersand. For example, if E2 contains the threshold 100, the formula below counts values at least as large as E2.

With amounts in C2:C20 and the threshold in E2, enter: =COUNTIF(C2:C20,">="&E2).

Combining IFS with COUNTIF

Use a combined formula when you want to turn one or more counts into a label or decision. Consider the seven status entries in A2:A8 described above. If two or more are “Overdue,” the report should say “Urgent.” Otherwise, if at least one is “Pending,” it should say “Follow up.” If neither condition is met, it should say “Clear.”

With statuses in A2:A8, enter: =IFS(COUNTIF(A2:A8,"Overdue")>=2,"Urgent",COUNTIF(A2:A8,"Pending")>0,"Follow up",TRUE,"Clear"). For the sample statuses, the result is “Urgent”: there are two overdue entries, so the first test is true and IFS returns its paired label.

This example also shows why condition order matters inside IFS. If the “Pending” test came first, the sample would return “Follow up” as soon as Excel found two pending entries, even though the overdue rule is meant to take priority. Put the rule that should win first.

A combined formula is useful for a compact dashboard, but it can become difficult to read if it repeats long ranges or many criteria. For a report that others need to inspect, put the individual counts in clearly labeled helper cells, then use IFS to evaluate those cells. This separates the counting logic from the decision logic and makes it easier to check each step.

When to use COUNTIFS instead

COUNTIF accepts one criterion. If a count must satisfy two or more conditions at the same time, use COUNTIFS. For example, suppose statuses are in A2:A8 and corresponding amounts are in B2:B8. The following formula counts rows that are both Paid and at least 100 in amount.

With statuses in A2:A8 and amounts in B2:B8, enter: =COUNTIFS(A2:A8,"Paid",B2:B8,">=100").

Each additional condition in COUNTIFS is supplied as another range-and-criterion pair. The ranges should cover corresponding rows and be the same size; otherwise, Excel can return a range-size error. The conditions are combined with an AND relationship: a row must meet every supplied condition to be counted.

Do not confuse that with using several COUNTIF calls inside IFS. In the combined example, each count is calculated separately, and IFS decides which result to return. Use COUNTIFS when a single count itself needs multiple filters. Use IFS when the output depends on which of several tests succeeds.

Criteria, wildcards, and common counting details

Text criteria in COUNTIF are not case-sensitive: “paid” matches “Paid.” For partial text matches, the asterisk wildcard represents any number of characters, and the question mark represents one character. If the text to find itself contains a wildcard symbol, place a tilde before that symbol to search for it literally.

With department names in D2:D50, this formula counts cells whose text begins with “North,” such as “North” or “North Region.”

With department names in D2:D50, enter: =COUNTIF(D2:D50,"North*").

Blank criteria can be useful but may surprise you. A criterion of an empty string can count blank cells, including cells whose formulas return an empty string. If your intention is to count cells that visibly contain content, choose a criterion that reflects that need and check how formulas in the range behave. Also make sure text criteria are enclosed in straight double quotation marks, and comparison operators are inside the criterion string when they are part of a fixed test.

Common errors and ways to troubleshoot

  • Unexpected IFS result: Review the test order. A broad condition placed too early can match before a more specific condition is reached.
  • #N/A from IFS: Check whether any test is true for the input. Add a final catch-all condition such as TRUE paired with an appropriate default result if one is needed.
  • Zero from COUNTIF: Check for extra spaces, inconsistent spellings, or a range that does not include the relevant cells. Text matching ignores capitalization, but it does not generally ignore additional spaces.
  • Criteria with a cell reference do not work: When combining a comparison operator with a referenced threshold, join them with &. A criterion such as ">="&E2 is different from putting the cell reference inside quotation marks.
  • Formula shows as text: Check whether the cell is formatted as Text or whether an apostrophe was entered before the equals sign. Change the cell format if needed, then re-enter the formula.

When checking a long combined formula, test its parts separately. First verify the COUNTIF results in temporary cells. Then confirm the order and thresholds used by IFS. This makes it easier to tell whether an unexpected label comes from a count, a criterion, or the decision order.

Compatibility and alternatives

IFS is available in Microsoft 365 and modern perpetual Excel releases, including Excel 2019, Excel 2021, and Excel 2024. COUNTIF is available in much older Excel versions as well. If a workbook needs to work in an older release that does not recognize IFS, Excel may show #NAME?. A nested IF formula can provide the same kind of ordered decision logic.

With a score in B2 and the same grade thresholds, a nested-IF alternative is: =IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","Below C"))). For a score of 84, the result is “B.” Nested formulas can be harder to read as the number of conditions grows, so keep the tests ordered and verify each closing parenthesis.

In Excel for Microsoft 365 on Windows, enter formulas directly in a worksheet cell or the formula bar and press Enter. The formulas above use commas as argument separators. Excel’s regional settings may require semicolons instead; if a comma-separated formula is rejected, check the list-separator setting used by that installation. Excel for Mac and Excel for the web support these standard functions too, though regional separators can vary there as well. The formula logic does not require a different menu path on those platforms.

Frequently Asked Questions

Can I use IFS and COUNTIF in the same Excel formula?
Yes. Use COUNTIF inside an IFS test when a decision depends on how many cells meet a criterion. For example, the combined formula above returns a status label based on overdue and pending counts.

What is the difference between IFS and IF?
IF evaluates one logical test and returns one value if it is true and another if it is false. IFS lets you check several test-and-result pairs in sequence, returning the result for the first true test.

Can COUNTIF check more than one condition?
Not in one call; COUNTIF takes one criterion. Use COUNTIFS to count rows that meet multiple conditions at once, or combine separate counts with other logic when the problem calls for it.

Does IFS need a final TRUE condition?
Not always, but it is useful as a default. If none of the listed tests is true and there is no catch-all pair, IFS returns #N/A.

Why does COUNTIF return zero when the text looks like a match?
Look for leading or trailing spaces, a misspelling, a range that omits the cell, or a criterion that is not written as intended. Capitalization alone is not usually the cause because COUNTIF text matching is case-insensitive.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.