Looking for a simple formula in Excel to count in column E (where we are entering…
- User
Looking for a simple formula in Excel to count in column E (where we are entering formula) how many different values in column B are for each value in column A.
- Assistant
To count how many different (unique) values in column B correspond to each value in column A, you can use a combination of Excel functions. Here's a simple formula you can use in column E:
Formula
For row 2 (assuming row 1 is a header), enter the following formula in cell E2:
=COUNT(DISTINCT(FILTER(B:B, A:A=A2)))However, since the
DISTINCTfunction is not available in Excel, we need to use an alternative approach withUNIQUE(available in Excel 365 and Excel 2021) or a helper array formula for older versions.For Excel 365/2021 (using UNIQUE):
In cell E2, use:
=ROWS(UNIQUE(FILTER(B:B, A:A=A2)))This formula:
FILTER(B:B, A:A=A2)filters column B to show only the values where column A matches the value in A2.UNIQUE(...)extracts only the distinct values from the filtered list.ROWS(...)counts the number of unique values.
For Older Versions of Excel (without UNIQUE):
If you're using an older version of Excel, you can use an array formula. In cell E2, enter:
=SUM(1/COUNTIFS(A:A, A2, B:B, B:B))Important: After typing this formula, press
Ctrl + Shift + Enterto make it an array formula (Excel will add curly braces{}around it). This formula:COUNTIFS(A:A, A2, B:B, B:B)counts how many times each value in B appears for the corresponding value in A.1/...inverts the counts to avoid double-counting duplicates.SUM(...)adds up the unique contributions.
Steps to Use
- Place the formula in cell E2.
- Copy the formula down column E for each row in your data.
Example
If your data looks like this:
A B E (Formula Result) Apple Red 3 Apple Green 3 Apple Red 3 Apple Blue 3 Banana Yellow 2 Banana Green 2 Banana Yellow 2 - For "Apple", there are 3 unique values in column B (Red, Green, Blue), so E2:E4 shows 3.
- For "Banana", there are 2 unique values in column B (Yellow, Green), so E5:E7 shows 2.
Notes
- If you're using Excel 365 or 2021, the
UNIQUEformula is simpler and more efficient. - For older versions, the array formula works but may be slower with very large datasets.
- Ensure your data does not have blank rows, or adjust the ranges (e.g.,
A2:A100instead ofA:A) to avoid errors.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.