14 Replies Latest reply on Dec 5, 2018 7:59 AM by Jessica Singer

    Count unique IDs IF contents of other columns >X

    Jessica Singer

      I need to create a calculation where I sum all of the unique member IDs that meet particular criteria contained in other cells. Ultimately, I'd like to know how many Member IDs Attended >1 day AND >=2 Locations. In English, I'd like to know how many participants played at more than one location, more than one time. I'm interested in knowing what percentage of our players play at multiple locations--but a lot of players will play ONE DAY at another location for a tournament, etc. I want to exclude these.

       

      I know that it's just a nested calc, but I can't figure it out.

       

      My source file is full of sensitive info, and the file itself is huge. I've attached a screen shot that should help with visualization. Let me know if you need more. The current AGG in the marks card is Count of Attendance Days, which is Count([Attendance]), and Attendance is just a list of individual dates that participants signed in to the location.

       

      EDIT: I have attached a sample Excel file, with names redacted.

       

      Message was edited by: Jessica Singer

       

      Message was edited by: Jessica Singer