Combine results of multiple COLLECTs? Or alternate strategy?

I am trying to get a count of trainees in my app, each trainee has a unique "Member ID". A trainee is defined as meeting one or more of the following criteria:

has a value of "Submitted" or "Issued" in the "Status" column of the "Reimbursements - Approved" sheet
has the current grant year (e.g., "GYr 4") in the "Mentee" column of the "Member Roster" sheet
has the current grant year (e.g., "GYr 4") in the "Resident" column of the "Member Roster" sheet

I have the following 3 COUNT/DISTINCT/COLLECT statements which all work fine individually, but I need to find the count of distinct MEMBER IDs from the results of all 3 COLLECTs combined:

=COUNT(DISTINCT(COLLECT({REIMB APPROVED | Member ID}, {REIMB APPROVED | Status}, OR(@cell = "Submitted", @cell = "Issued"))))

=COUNT(DISTINCT(COLLECT({MEMBER ROSTER | Member ID}, {MEMBER ROSTER | Mentee}, [Current Grant Year]@row)))

=COUNT(DISTINCT(COLLECT({MEMBER ROSTER | Member ID}, {MEMBER ROSTER | Resident}, [Current Grant Year]@row)))

In SQL, I could use UNIONs to combine the results of all 3 COLLECTS, then select the distinct Member IDs from those combined results. Is something like this possible in Smartsheet?

Find more posts tagged with

Formulas

Accepted answers

All comments

Hollie Green

I would try the below

=Sum(COUNT(DISTINCT(COLLECT({REIMB APPROVED | Member ID}, {REIMB APPROVED | Status}, OR(@cell = "Submitted", @cell = "Issued")))),COUNT(DISTINCT(COLLECT({MEMBER ROSTER | Member ID}, {MEMBER ROSTER | Mentee}, [Current Grant Year]@row))),COUNT(DISTINCT(COLLECT({MEMBER ROSTER | Member ID}, {MEMBER ROSTER | Resident}, [Current Grant Year]@row))))

RicqP

Thanks for the suggestion Hollie. A member can fall into more than one category at the same time so unfortunately I think this wouldn't eliminate duplicate occurrences of Member IDs. Ex: a member might have a reimbursement status of "Submitted" but also be a Mentee.

I'm hoping to accomplish something like this:

Get all Member IDs where reimbursement is Submitted or Issued (from Reimb-Approved sheet)
plus
Get all Member IDs where member is Mentee (from Member Roster sheet)
plus
Get all Member IDs where member is a Resident (from Member Roster sheet)

So now I will have a pool of all Member IDs although some will be duplicated. Now remove all duplicate Member IDs from that pool, then get a count of unique remaining Member IDs.

Jason Tarpinian

Sounds like a helper column on your source sheet should help you out. Whenever I have a problem where I don't want to double count a record, build a helper column with your COLLECT criteria. In your situation, something like a checkbox column that you could simply then count.

=IF(AND(OR([REIMB Approved | Member ID]@row = "Submitted", [REIMB Approved | Member ID]@row = "Issued"), [MEMBER ROSTER | Mentee]@row = [Current Grant Year]@row, [MEMBER ROSTER | Resident]@row = [Current Grant Year]@row), true)

Depending on your setup, you may have to pull in the "Current Grant Year" from your other sheet, if that's calculated somehow special. And also sounds like you're trying to COLLECT 2 different ranges though (maybe it's too late on a Friday for me to understand why multiple ranges) so I'm not sure how that plays into not counting duplicates? Hopefully this helps to at least gets you close, the gist is that you do the easy formula on your source sheet and count from that.

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.