# Countifs Name reference

✭✭✭✭✭

We are working on a formula where we are needing the counts of two names in a column. So if the range column we are pulling from has both John Doe and Jane-Marie Doe, we want the count of how many times that occurs.

This is the formula we have and it is not working:

=COUNTIFS([{A Great Place to Work Range 4}]:[{A Great Place to Work Range 4}], "John Doe", {A Great Place to Work Range 4}:{A Great Place to Work Range 4}, "Jane-Marie Doe"))

We are also trying to do the same thing, but with only one name and it is not working:

=COUNTIFS([{A Great Place to Work Range 4}]:[{A Great Place to Work Range 4}], "John Doe"))

Tags:

• ✭✭✭✭✭

We are getting the unparseable error

• ✭✭✭✭✭✭

There are a few issues with the above...

When you are creating cross sheet references, you do not use square brackets, and you do not double it up with the colon to reflect a column (or range of columns). If you want to reference the entire column called "Name", then you would select the column header for the Name column from the other sheet when creating the reference, and it would appear inside of curly brackets. That is the range for the formula.

=COUNTIFS({A Great Place to Work Range 4}, "John Doe", {A Great Place to Work Range 4}, "Jane-Marie Doe"))

You also have one too many closing parenthesis at the end of your formula.

=COUNTIFS({A Great Place to Work Range 4}, "John Doe", {A Great Place to Work Range 4}, "Jane-Marie Doe")

Additionally, your formula is currently saying to count if the cell EQUALS "John Doe" AND EQUALS "Jane-Marie Doe". A cell cannot equal two different values at the same time. To correct this, we would need to establish EXACTLY what you are trying to count.

Is it ONLY if it contains BOTH names or is it if it contains at least one of those names? Do you want to also count if it contains other names in addition to the previous?

Which rows would you want counted and why?

1. John Doe
2. Jane-Marie Doe
3. John Doe, Jane-Marie Doe
4. John Doe, John Smith
5. Jane-Marie Doe, Jane Smith
6. John Doe, Jane-Marie Doe, John Smith
• ✭✭✭✭✭

So the row we are counting, there is the ability to select multiple people within that cell that is why we are wanting to see John Doe AND Jane-Marie Doe (or just Jane Doe for simplicity).

We are wanting to know how many projects they are working on together.

• ✭✭✭✭✭✭

So would that be #'s 3 and 6 or just 3?

• ✭✭✭✭✭

It would be #3

• ✭✭✭✭✭✭

Ok. And is it a Contact Type column?

• ✭✭✭✭✭

It is indeed a contact field.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!