# Help needed with an If/And formula referencing another sheet

✭✭✭✭
edited 12/09/19

Thank you for taking a look at this.

I have one sheet where I am trying to show the count for a person on another sheet IF another column has a 1 in it. The 1 represents a checkbox in the ajoining column. For some I have tried looking for the specific name (where you see Courtney) and others had the name listed in a column on the totals sheet (where you see Sales14)

Here are some (but not all) of the formulas I have tried. I am getting frustrated. I know I'm missing something simple but I think I've stared at it too long now to make any sense of it. Any help you can give will be most appreciated!

Thank you!

=COUNTIFS(AND({2017 Q1/2 Rates Queue Range 1, Sales14}, {2017 Q1/2 Rates Queue Range 2}, 1))

=COUNTIFS(AND({2017 Q1/2 Rates Queue Range 1}, {2017 Q1/2 Rates Queue Range 2}, 1))

=COUNTIFS(AND({2017 Q1/2 Rates Queue Range 1}, Courtney, {2017 Q1/2 Rates Queue Range 2}, 1))

=COUNTIFS(AND({2017 Q1/2 Rates Queue Range 1}, Sales14, {2017 Q1/2 Rates Queue Range 2}, 1))

=COUNTIF({2017 Q1/2 Rates Queue Range 1}, Courtney, {2017 Q1/2 Rates Queue Range 2}, 1)

=COUNTIF({2017 Q1/2 Rates Queue Range 1}, Sales14, {2017 Q1/2 Rates Queue Range 2}, 1)

• ✭✭✭✭✭✭

COUNTIFS essentially already has AND built into it. COUNTIF does not. Try one of your last two formulas except as COUNTIFS.

Other than that, those last two formulas look sound as far as I can tell without actually seeing the sheets.

• ✭✭✭✭

I'm still not getting it. I am including two images that maybe will help.

In my initial post I used Courtney, in the examples I used Chasity because she's towards the top of the list and I wanted to make sure the example showed that there should be a count for that person.

Just in case, I tried this without the Primary Column in use and received same results.

• ✭✭✭✭✭✭

First example, Chasity is text and needs to be in quotes "Chasity"

Craig

• ✭✭✭✭

Craig,

Thank you, that did it! I knew it was something silly I was missing. Thank you again.

Have a great day.

• ✭✭✭✭✭✭

I just spotted the typo in the second one.

You misplaced the closing curly brace at the end.

Craig

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!