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
Answers
-
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)))
-
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
-
Did you create the second cross sheet reference?
-
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)))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!