Count the number of projects assigned per person via a multi-contact cell

abirobson

Hi,

I have a main sheet which tracks all the different projects going on. One of those columns labelled 'CM' tracks who has been assigned. It is restricted to contact values, sometimes there is 1 contact assigned, and sometimes there is 3.

I want to be able to insert a graph on my dashboard which shows the number of projects assigned to each person, however I am struggling with the formula on my metrics sheet.

When I use the COUNTIF function, it only counts the first contact listed (e.g., if I am listed second in the list on the original sheet, it will not be counted). I have tried a range of different formulas but am unsure how to get it to count all contacts listed?

Any help is greatly appreciated!

Eric Law

Hello @abirobson

You can use =COUNTIF(Contact:Contact, CONTAINS("Contact Name"))

abirobson

Hi @Eric Law thanks for getting back to me. Unfortunately that does not work, I think there is a problem with it referencing other sheets potentially?

Paul Newcome

@Eric Law Your CONTAINS function is missing the range.

But... The CONTAINS function does not work with contacts. You have to use either the FIND or the (preferred) HAS function.

@abirobson Give this a try:

=COUNTIFS({Range}, HAS(@cell, "Contact Name"))

abirobson

Hi @Paul Newcome,

I have tried the above formula but it's pulling through all as 0. Perhaps I am inputting this wrong, but surely adding in the 'HAS' means that the COUNTIF function is not finished? Screenshot of formula below where MS WEEKLY Stats Range 3 is the column counting the contacts and Primary Column '@ row is the individual name

Paul Newcome

The "range" inside of the HAS function should be @cell exactly as I have it in my example.

abirobson

Hi @Paul Newcome,

Unfortunately still pulls through a 0!

abirobson

Hi @Paul Newcome,

Started a fresh metrics sheet and redid all my formulas from my original sheet and seems to be working now :)

Thanks for your help!!

Paul Newcome

Happy to help. 👍️