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

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!

Find more posts tagged with

Accepted answers

All comments

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.