Help needed with an If/And formula referencing another sheet

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)

Find more posts tagged with

Formulas

Project Management

Comments

Paul Newcome

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

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

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

Craig

Angie Hatfield

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

I just spotted the typo in the second one.

You misplaced the closing curly brace at the end.

Craig

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?