Help needed with an If/And formula referencing another sheet

Angie Hatfield · 08/29/18

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)

Paul Newcome · 08/29/18

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.

Angie Hatfield · 08/30/18

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.

Thank you for your help.

J. Craig Williams · 08/30/18

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

Craig

Angie Hatfield · 08/30/18

Craig,

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

Have a great day.

J. Craig Williams · 08/30/18

I just spotted the typo in the second one.

You misplaced the closing curly brace at the end.

Craig

Help needed with an If/And formula referencing another sheet

Comments

Help Article Resources

Categories