Results 1 to 2 of 2

Thread: [RESOLVED] How to count unique items in a range (Excel)

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    505

    Resolved [RESOLVED] How to count unique items in a range (Excel)

    I have the following worksheet, named "Event Log".

    Name:  2024-09-14_6-39-22.jpg
Views: 134
Size:  24.9 KB

    I would like to count the Date field for a specific Species (column E) and return the count of unique Dates (column A). For example, in the above image there are 7 instances of the "Fox" species (4 on 1/3/2024 and 3 on 1/4/2024) but only 2 unique dates (1/3/2024 and 1/4/2024).

    I would like to do this via a function instead of via VBA code.

    I found a link (https://exceljet.net/formulas/count-...-with-criteria) that shows how to count unique numeric values with criteria, but I can't get it to work.

    For example, the following should return a value of 2 since there are 2 unique dates (1/3/2024 and 1/4/2024) in the specified range that have "Fox" species...

    Code:
    {=SUM(--FREQUENCY(IF(('Event Log'!E17:E23= "Fox" ),'Event Log'!A17:A23),'Event Log'!A17:A23)>0)}
    But the above example returns a value of 0, not 2.

    Looking for any helpful ideas, examples, suggestions, etc.

    Thanks.
    Last edited by Mark@SF; Sep 14th, 2024 at 08:21 AM.

  2. #2

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    505

    Re: How to count unique items in a range (Excel)

    I figured out the problem. I was missing a pair of parenthesis. Here is the correct formula.

    Code:
    {=SUM(--(FREQUENCY(IF('Event Log'!E17:E23="Fox",'Event Log'!A17:A23),'Event Log'!A17:A23)>0))}

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width