Countifs Name reference

Maria Watters

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

Paul Newcome

Ok. So we want to FIND "John Doe" and FIND "Jane-Marie Doe" but we only want to count if those two are the only two in the cell.

FIND("John Doe", @cell) > 0

FIND("Jane-Marie Doe", @cell) > 0

LEN(@cell) = 23

The LEN function ensures that it will not count cells that have both names as well as additional names in it.

=COUNTIFS({A Great Place to Work Range 4}, AND(FIND("John Doe", @cell) > 0, FIND("Jane-Marie Doe", @cell) > 0, LEN(@cell) = 23))

Maria Watters

We are getting the unparseable error

Paul Newcome

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

Maria Watters

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.

Paul Newcome

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

Maria Watters

It would be #3

Paul Newcome

Ok. And is it a Contact Type column?

Maria Watters

It is indeed a contact field.

Paul Newcome

Ok. So we want to FIND "John Doe" and FIND "Jane-Marie Doe" but we only want to count if those two are the only two in the cell.

FIND("John Doe", @cell) > 0

FIND("Jane-Marie Doe", @cell) > 0

LEN(@cell) = 23

The LEN function ensures that it will not count cells that have both names as well as additional names in it.

=COUNTIFS({A Great Place to Work Range 4}, AND(FIND("John Doe", @cell) > 0, FIND("Jane-Marie Doe", @cell) > 0, LEN(@cell) = 23))