Skip to content
TheChatArchiveThe Chat Archive
Qwenqwq-32b· Alibaba (Qwen)Hugging Face LMArena Human Preference 140K

Looking for a simple formula in Excel to count in column E (where we are entering…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with grok-3-preview-02-24’s answer.
  1. 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.

  2. Qwen 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:

    1. Enter this formula in cell E2 (assuming your data starts from row 2).
    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 FILTER and UNIQUE.
    • If your data has headers, adjust the ranges (e.g., B2:B100 instead of B:B) to avoid including headers in the calculation.

    Example:

    ABE (Formula Result)
    AppleRed2
    AppleGreen2
    BananaYellow1
    AppleRed2

    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.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.