Count Ifs Formula Multiple Criteria

Will Hughes

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

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