Skip to content
TheChatArchiveThe Chat Archive
grok-3-preview-02-24· xAIHugging 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 Qwenqwq-32b’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. 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 DISTINCT function is not available in Excel, we need to use an alternative approach with UNIQUE (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:

    1. FILTER(B:B, A:A=A2) filters column B to show only the values where column A matches the value in A2.
    2. UNIQUE(...) extracts only the distinct values from the filtered list.
    3. 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 + Enter to make it an array formula (Excel will add curly braces {} around it). This formula:

    1. COUNTIFS(A:A, A2, B:B, B:B) counts how many times each value in B appears for the corresponding value in A.
    2. 1/... inverts the counts to avoid double-counting duplicates.
    3. SUM(...) adds up the unique contributions.

    Steps to Use

    1. Place the formula in cell E2.
    2. Copy the formula down column E for each row in your data.

    Example

    If your data looks like this:

    ABE (Formula Result)
    AppleRed3
    AppleGreen3
    AppleRed3
    AppleBlue3
    BananaYellow2
    BananaGreen2
    BananaYellow2
    • 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 UNIQUE formula 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:A100 instead of A:A) to avoid errors.

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

Identical first prompt