Count Based on other Distinct Data

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?

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

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?

em_sfcc

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.

Paul Newcome

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")))

em_sfcc

Got it - Thank you!

Paul Newcome

Happy to help. 👍️

Kevin Bernard

Hi @Paul Newcome,

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!

Paul Newcome

@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?

Kevin Bernard

@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".

Paul Newcome

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"?

Kevin Bernard

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.

Paul Newcome

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.

Elizabeth Sykes

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?

Unique District Attendance Count.png

Paul Newcome

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.