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 the number of distinct values in column B for each value in column A and display the result in column E, you can use the following Excel formula:
Formula:
=COUNTA(UNIQUE(FILTER(B:B, A:A = A2)))Instructions:
- Enter this formula in cell E2 (assuming your data starts from row 2).
- Drag the formula down to fill the rest of the cells in column E.
How It Works:
- FILTER(B:B, A:A = A2): Filters column B to include only the rows where the corresponding value in column A matches the value in A2.
- UNIQUE(...): Extracts the unique values from the filtered list of B.
- COUNTA(...): Counts the number of non-empty (distinct) values in the result.
Notes:
- This formula works with Excel 365 or Excel 2021 due to the use of dynamic array functions like
FILTERandUNIQUE. - If your data has headers, adjust the ranges (e.g.,
B2:B100instead ofB:B) to avoid including headers in the calculation.
Example:
A B E (Formula Result) Apple Red 2 Apple Green 2 Banana Yellow 1 Apple Red 2 In this example:
- For "Apple" in column A, there are 2 distinct values in column B (Red and Green).
- For "Banana", there is 1 distinct value (Yellow).
This formula efficiently counts unique values dynamically for each entry in column A.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.