Need to Count Distinct Based on Multiple Criteria

Hi there!

I have a database (Sheet 1) where I log all my company's gifting to social media influencers and the subsequent exposure on Social Media in one sheet. I organize them by the Name of Influencer, the month we gifted, the month they posted (if they did, otherwise its blank), among others.

In a different sheet (Sheet 2), I am trying to count distinct the Name of the influencer we gifted from (Sheet 1). But I want it to be interactive so within Sheet 2, I have a multiple dropdown cell I will list all the months I am interested in evaluating. For example, if I wanted to see the distinct count of names that we gifted in January, February, and March I would select those three months in that dropdown menu.

I am able to to the distinct count without any problems when it is just one month to evaluate but when it becomes multiple values, the formula below does not work.

=COUNT(DISTINCT(COLLECT({DD Influencer Database Range 1}, {DD Influencer Database Range 2}, Joined@row)))

Any solutions to this?

Thank you!

Find more posts tagged with

Sheets

Formulas

Reports

Cell linking

Accepted answers

Bassam Khalil

Hi @Amt501

I fixed as you need by using multiselect option, please check it:

=IFERROR(COUNT(DISTINCT(COLLECT({Name}, {Gifts Sent}, OR(@cell = Joined4, @cell = Joined5,
@cell = Joined6, @cell = Joined7, @cell = Joined8, @cell = Joined9, @cell = Joined10,
@cell = Joined11, @cell = Joined12, @cell = Joined13, @cell = Joined14, @cell = Joined15)))), "")

the following screenshot shows the result

All comments

Bassam Khalil

Hi @Amt501

Hope you are fine, please try the following formula ( i chose Jan,Feb,Mar) as a criteria:

=COUNT(DISTINCT(COLLECT({DD Influencer Database Range 1}, {DD Influencer Database Range 2},
OR(CONTAINS("Jan", @cell), CONTAINS("Feb", @cell), CONTAINS("Mar", @cell)))))

Amt501

Hi @Bassam Khalil ,

Thank you for your comments!

However, I was wondering if there can be a solution where " Jan" , " Feb" , and "Mar" are not inputs into a formula.

That is, I want the formula to reference a dropdown list to see which months to include. So when that dropdown changes months to Mar, Apr, May the formula updates automatically.

Is that possible?

Thank you!

Bassam Khalil

@Amt501

Yes we can do that, if you like me to fix the formula directly on your sheet please share me as an admin on a sample copy of your sheets ( Source & Destination ) and i will Create the exact formula for you then you can copy it to your original sheet.

My Email for sharing : Bassam.k@mobilproject.it

Amt501

@Bassam Khalil I Have created a new workspace and invited you as admin to the space.

Thank you for your help!

Bassam Khalil

@Amt501

Ok i will check it .

Bassam Khalil

Hi @Amt501

I fixed as you need by using multiselect option, please check it:

=IFERROR(COUNT(DISTINCT(COLLECT({Name}, {Gifts Sent}, OR(@cell = Joined4, @cell = Joined5,
@cell = Joined6, @cell = Joined7, @cell = Joined8, @cell = Joined9, @cell = Joined10,
@cell = Joined11, @cell = Joined12, @cell = Joined13, @cell = Joined14, @cell = Joined15)))), "")

the following screenshot shows the result

Amt501

@Bassam Khalil Thank you very much. This works perfectly!!

Amt501

Thank you!

Bassam Khalil

@Amt501

You are welcome and I will be happy to help you any time

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.