How to Count Unique Values in a Row in Excel Easily

Formulas & functions · Updated November 18, 2026

To count distinct, nonblank values across a row in Microsoft 365, use =IFERROR(COLUMNS(UNIQUE(FILTER(B2:K2,B2:K2<>""))),0). This assumes the values are in B2:K2; it filters out blanks, finds the unique entries, and counts them. For example, if the row contains red, blue, red, a blank, and green, the result is 3. The formula works in Microsoft 365 for Windows, Mac, and Excel for the web.

Counting unique values is useful when a row contains repeated names, products, categories, or responses and you need to know how many different entries appear. The instructions below count nonblank values and treat text that differs only by capitalization, such as “North” and “north,” as the same value, consistent with Excel’s usual comparisons.

What You’ll Need

  • An Excel workbook with the values you want to check in a single row.
  • A blank cell for the count. Choose a cell outside the data range so the result does not overwrite an entry.
  • Microsoft 365 or Excel 2021 for the UNIQUE and FILTER formula. If you use an older version, see the alternative in Step 6.

Step-by-Step

  1. Identify the row and its boundaries. Find the first and last cells that should be included. For example, if a person’s responses are in B2 through K2, the range is B2:K2. Include the full intended range, including cells that may be blank between entries. Do not select the entire worksheet row unless you genuinely need to check all 16,384 columns; a smaller range is easier to understand and calculate.
  2. Choose a result cell. Click a blank cell outside the range, such as L2 if the data ends at K2. If the row is part of a table or report, consider placing a clear label such as “Unique count” in the cell above the result. Keeping the count separate from the source values prevents the formula from replacing data and makes the result easier to find.
  3. Enter the formula for your range. With values in B2:K2 and the result cell outside that range, type or paste =IFERROR(COLUMNS(UNIQUE(FILTER(B2:K2,B2:K2<>""))),0) and press Enter. FILTER removes cells that are empty; UNIQUE returns one copy of each remaining value; COLUMNS counts the returned horizontal entries. IFERROR displays 0 if the row has no qualifying entries, rather than showing the no-match error that FILTER would otherwise return.
  4. Check the count against a small example. Suppose B2:K2 contains “red,” “blue,” “red,” a blank, and “green,” with the remaining cells blank. The formula returns 3 because the nonblank distinct entries are red, blue, and green. If the row instead contains only blanks, it returns 0. A quick manual check on a short row helps confirm you selected the right cells and that blank entries should be excluded.
  5. Copy the formula to other rows if needed. If each row has its own set of entries, copy the result cell down using the fill handle at its lower-right corner, or copy and paste it into the result cells below. Excel adjusts the row references automatically: the formula for row 3 will check B3:K3. After copying, inspect a few results to ensure each formula points to the matching source row and not to a fixed row by mistake.
  6. Use a compatible alternative in older Excel. UNIQUE and FILTER require Excel 2021 or Microsoft 365. If you have values in B2:K2 and use an older desktop Excel version, you can try =SUMPRODUCT((B2:K2<>"")/COUNTIF(B2:K2,B2:K2&"")) in a result cell. For the example with red, blue, red, a blank, and green, it returns 3. This approach has edge cases with text containing wildcard characters such as an asterisk or question mark, and it can be affected by errors in the source range. If those cases matter, use a newer Excel version or clean the data before counting.
  7. Save the workbook and label the result. A formula updates when the source values change, so the count remains useful as the row is edited. Add a nearby heading such as “Distinct entries” if the count may be read by someone who did not create the sheet. If the row has a fixed maximum number of entries, keep the range wide enough to include future entries while avoiding unnecessary full-row calculations.

Common Mistakes

Including the result cell in the source range: If your formula sits inside the cells it is counting, it may include its own result or create a circular reference. Put the answer in a separate cell and confirm that the selected range contains only the source data.

Counting blanks as values: UNIQUE by itself can return a blank entry when the source includes empty cells. That is why the main formula filters out empty cells first. If you actually want a blank to count as one distinct item, do not use the blank-filtering version; decide explicitly how empty cells should be treated before changing the formula.

Assuming capitalization creates a new value: Excel’s ordinary text comparisons are not case-sensitive for this kind of count. “Item” and “ITEM” count as one value. If your data uses capitalization to represent genuinely different categories, standardize or transform the values before counting, or use a deliberately case-sensitive approach rather than relying on UNIQUE’s default behavior.

Using the wrong row or an incomplete range: A formula can be syntactically correct but still give an incorrect answer if it starts one column too late or stops before the final entry. Click the formula cell and review the colored range highlight, or select the reference in the formula bar to check its boundaries.

Overlooking spaces and inconsistent entries: “Blue” and “Blue ” (with a trailing space) may be counted separately because the stored text is not identical. Similarly, a number stored as text can behave differently from a numeric value. Clean inconsistent source values if they are meant to represent the same category.

Tips

Use a range that reflects the data layout. If entries may be added later, include spare cells at the end of the row, but avoid selecting every column simply for convenience. For a repeated report with the same number of columns each month, keep the range consistent so the formula is easy to audit and copy.

For data in a structured Excel table, you can still put the count in a nearby cell and refer to the row’s relevant cells, but a row-based range such as B2:K2 is often simplest for a horizontal list. If you copy formulas across columns as well as down rows, consider whether the references should move in both directions before filling. A dollar sign can lock a reference when needed, but do not lock the row if each copied formula must count a different row.

Excel’s formula separator depends on regional settings. The examples use commas, as in English-language Excel. If Excel rejects a formula even though the function names are recognized, your installation may expect semicolons between arguments. Follow the separator shown by Excel’s formula suggestions or settings rather than changing the logic of the formula.

For a one-time check, you can also visually inspect the row or use a filter to review its entries, but a formula is more reliable when the source changes often. Keep the source values intact: deleting duplicates from the original row is not necessary to get a count and could remove information you still need.

Troubleshooting

The formula shows #NAME? or does not recognize a function. Check that your Excel version supports UNIQUE and FILTER. Those functions are available in Microsoft 365 and Excel 2021 or later, including current Excel for Mac and Excel for the web. In an older version, use the alternative in Step 6 or open the workbook in a supported version. Also check for a misspelled function name.

The result is 0, but the row looks populated. Confirm that the formula references the cells containing the data, not a neighboring row. If the cells contain formulas that return an empty string, the main formula treats those results as blank. If a cell appears to contain a value but is actually spaces, it is not empty; remove the spaces or clean the source. Also check whether the visible content is in a different range than the one in the formula.

The result is larger than expected. Look for trailing spaces, punctuation differences, inconsistent spelling, or values stored with different data types. For instance, “East” and “East ” can count separately. Edit the source or clean it consistently before relying on the count. If you use the older COUNTIF alternative, check for literal asterisks or question marks in text because COUNTIF treats those characters as wildcards in criteria.

You see #CALC! or another error. The IFERROR wrapper in the recommended formula handles the no-match case, such as a row containing only blanks. If an error remains, verify that the formula was entered as shown, that the range is valid, and that source cells do not contain errors that are being passed through the calculation. Correct or remove source errors if they should not be part of the count.

Excel displays the formula as text instead of calculating it. The result cell may be formatted as Text, or the entry may begin with an apostrophe. Change the cell format to General, then re-enter the formula by selecting the cell, pressing F2, and pressing Enter. On a Mac, use Control+U to edit the active cell in many Excel configurations; if that shortcut differs in your setup, double-click the cell or use the formula bar instead.

Frequently Asked Questions

Does the formula count blank cells?
No. The recommended formula filters out empty cells and cells whose formulas return an empty string. If all cells in the selected range are blank, it returns 0. Cells containing spaces are not truly blank, so remove those spaces if they should be excluded.

Does Excel count uppercase and lowercase text separately?
No, not with the standard UNIQUE formula shown here. Text that differs only by capitalization is treated as the same value. If capitalization is meaningful in your data, you need a case-sensitive method or a consistent helper column that encodes the distinction.

Can I count unique values in several rows at once?
Yes. Put the formula in a result column beside the first row, then copy it down. Excel updates the row number in the reference for each copied formula. Check the first and last copied formulas to confirm they refer to the intended rows.

Will the count update when I change a value?
Yes. Excel recalculates the formula when a value in the referenced range changes, provided calculation is set to Automatic. If the count does not refresh, check Formulas > Calculation Options > Automatic, then recalculate the workbook if needed.

Can I count unique values in a column instead?
Yes. Use the same idea with a vertical range, such as B2:B20, and count the unique results with ROWS rather than COLUMNS. Keep the FILTER condition pointed at that same column range and exclude blanks if you do not want them included.

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.