# Count Based on other Distinct Data

✭✭
edited 07/01/20

I am doing end of year summary data and am stuck. I ran a report and have lots of data - name, gender, race from rosters across multiple trainings. There is duplicate data (X individual may have attended Y training, but also Z training). I need duplicated summary data - X counts as 1 person trained, 1 female trained, etc. Easy enough for names - I used COUNT(DISTINCT). But I am stuck on gender/race. I want to count unduplicaed males, and unduplicated females, etc. If this can be done using COUNTIFS, I haven't figured it out. Any suggestions?

• ✭✭✭✭✭✭

Can you create a mock up using "dummy data" and provide a screenshot of they data you are evaluating as well as how you want these counts to be laid out on your sheet?

• ✭✭

Since I am doing a separate report for multiple companies, I will have the summary data on a separate sheet, but I included it in this example and highlighted what I am looking to answer. For example, how do I count the number of males trained if Dave Jones attended 2 trainings? I keep thinking COUNTIFS, but cannot figure out how.

• ✭✭✭✭✭✭

You would use a COUNT/DISTINCT/COLLECT for the ones with extra criteria and a COUNT/DISTINCT for the overall.

=COUNT(DISTINCT({Source Sheet First+Last Column}))

=COUNT(DISTINCT(COLLECT({Source Sheet First+Last Column}, {Source Sheet Gender Column}, "Male")))

=COUNT(DISTINCT(COLLECT({Source Sheet First+Last Column}, {Source Sheet Gender Column}, "Female")))

=COUNT(DISTINCT(COLLECT({Source Sheet First+Last Column}, {Source Sheet Race Column}, "White")))

=COUNT(DISTINCT(COLLECT({Source Sheet First+Last Column}, {Source Sheet Race Column}, "Black")))

=COUNT(DISTINCT(COLLECT({Source Sheet First+Last Column}, {Source Sheet Race Column}, "Hispanic")))

• ✭✭

Got it - Thank you!

• ✭✭✭✭✭✭

Happy to help. 👍️

• This works really great for me with 2 criteria, however it seems to fall over if I add a 3rd. Is this method limited to 2 criteria? Seems I missed a detail along the way. Thanks!

• ✭✭✭✭✭✭

@Kevin Bernard It is only limited by the number of characters within a string (4,000/cell). What is your current formula and what are you trying to add?

• @Paul Newcome Thanks for the quick reply.

Current: =COUNT(DISTINCT(COLLECT({Client_Name}, {OBT_Usage}, "ClientA",

{OBT_Chain_Status}, [Primary Column]@row))

What I want: =COUNT(DISTINCT(COLLECT({Client_Name}, {OBT_Usage}, "ClientA",

{OBT_Chain_Status}, [Primary Column]@row, {OBTLevel}, “L0”))

The current formula lines up when I compare it via an Excel export, however the desired formula returns a value of "1".

• ✭✭✭✭✭✭

How is the data in the {OBTLevel} range populated? If it is manual entry, is it possible that some are entered as "el oh" instead of "el zero"?

• They are all "el zero". Was wondering this too. Feels like there's an issue when combining 3 criteria. I removed the {OBT_Usage} criteria to use the "el zero" one and it gave me a count that lined up with an excel export.

• ✭✭✭✭✭✭

I have many formulas that use well over 3 range/criteria sets. You may want to double check the data in the source sheet to confirm that there aren't any hidden spaces or apostrophes depending on how exactly the data is populated.

It could also be a bug within the sheet/column itself in which case you would want to loop in support.

• I'm am trying to do a distinct count of the number of districts who attended an event. I have a sheet with all registered attendees and column with a check box column to designate who actually attended an event. How would I get a distinct count of districts who attended the event?

• ✭✭✭✭✭✭

@Elizabeth Sykes You would want to use something along the lines of...

=COUNT(DISTINCT(COLLECT([District Name]:[District Name], Attended:Attended, 1)))

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!