Count Ifs Formula Multiple Criteria

Hi all,

I'm hoping you can help me! I'm trying to make a formula that counts unique email addresses (on another sheet) but only if they attended an event (marked in my sheet as 1 for attended, 0 for no show). This is what I've tried with no luck:

=COUNTIF(DISTINCT({WFW AUT Raw Data Range 1}, Attended:"1"))

'Data Range 1' is the column with the email addresses.

Any help is greatly appreciated!

Many thanks

Will

Find more posts tagged with

Accepted answers

All comments

Leibel S

@Will Hughes

See below correct syntax/formula.

Bolded below is a a new cross sheet reference you will need to make to reference the "Attended" column in your other sheet.

=COUNT(DISTINCT(COLLECT({WFW AUT Raw Data Range 1}, {WFW AUT Raw Data Range 1}, @cell <> "", {WFW AUT Raw Data - Attended Range}, 1)))

Will Hughes

Thanks for getting back to me so quickly Leibel. The number it generates is 1, when it should be over 300. I pasted:

=COUNT(DISTINCT(COLLECT({WFW AUT Raw Data Range 1}, {WFW AUT Raw Data Range 1}, @cell <> "", {WFW AUT Raw Data - Attended Range}, 1)))

and also tried:

=COUNTIF(DISTINCT({WFW AUT Raw Data Range 1}, {WFW AUT Raw Data - Attended Range}, 1))

and..

=COUNT(DISTINCT(COLLECT({WFW AUT Raw Data Range 2}{WFW AUT Raw Data Range 3}, "1")))

Thanks for your time,

Will

Cancel

Leibel S

@Will Hughes

Did you create the second cross sheet reference?

Will Hughes

Thanks for getting back to me Leibel. Happy to report I managed to get it to work! I'm still new to smartsheets so it's been a steep learning curve!

This is the formula I used

=COUNT(DISTINCT(COLLECT({WFW AUT Raw Data - with formulas Range 1}, {WFW AUT Raw Data - with formulas Range 2}, 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.