How to calculate the # of times a persons name is listed in multiple columns

sjohanson

Using "Nikki Pooler" as an example - her name is listed in "Responsible, Accountable, Production Operations"

The formula that works but ONLY pulls in the "Responsible" and " Accountable" columns is: =COUNTIF(Responsible:Accountable, HAS(@cell, "Nikki Pooler"))

How do I expand this to include the column "Production Operations"?

To note: All columns are multi select.

Nick Korna

Make a separate COUNTIF and add it to the original one.

So the extra one is:

=COUNTIF([Production Operations]:[Production Operations], HAS(@cell, "Nikki Pooler"))

Combined with the other:

=COUNTIF(Responsible:Accountable, HAS(@cell, "Nikki Pooler")) + COUNTIF([Production Operations]:[Production Operations], HAS(@cell, "Nikki Pooler"))

This should give you the total.

If you didn't mind the columns between being included, you would simply change the range on your existing formula:

=COUNTIF(Responsible:[Production Operations], HAS(@cell, "Nikki Pooler"))

Hope this helps, but let us know if I've misunderstood something or there are any problems/questions!

Nick Korna

Make a separate COUNTIF and add it to the original one.

So the extra one is:

=COUNTIF([Production Operations]:[Production Operations], HAS(@cell, "Nikki Pooler"))

Combined with the other:

=COUNTIF(Responsible:Accountable, HAS(@cell, "Nikki Pooler")) + COUNTIF([Production Operations]:[Production Operations], HAS(@cell, "Nikki Pooler"))

This should give you the total.

If you didn't mind the columns between being included, you would simply change the range on your existing formula:

=COUNTIF(Responsible:[Production Operations], HAS(@cell, "Nikki Pooler"))

Hope this helps, but let us know if I've misunderstood something or there are any problems/questions!

sjohanson

This worked! THANK YOU!

sjohanson

@Nick Korna - Follow up question, thinking through this a bit further.

Goal: Pull the # of projects an individual is assigned to from the source sheet (above screen shot) into another using a formula.

Example: In the screen shot above, each row represents 1 project. Using Nikki Pooler as an example, she is tagged in a total of 4 projects. I need to use a formula to pull the calculation of 4 into a separate sheet that I have for dashboard totals (see second screen shot).

Nick Korna

You would amend your COUNTIF formulas to COUNTIFS to add in the project.

Say your project column is simply named "Project" and you want the whole range from "Responsible" through to "Production Operations" for Project ABC:

=COUNTIFS(Project:Project,"ABC", Responsible:[Production Operations], HAS(@cell, "Nikki Pooler"))

If you just some of that range and then additional columns later, you would adapt the addition method used before in a similar fashion.

Obviously it would be extremely time consuming to do this for each individual person, so realistically I would probably do another sheet with some cross sheet references. Exactly how I'd lay it out would probably depend on how big your team to check is and what exactly you're looking to find (e.g. if you're trying to make sure nobody is duplicated in roles and you've only a few people I would make a list of the projects and give each person a single column and have something like this (using Nikki as the example):

= COUNTIFS({Project},Project@row,{Range of areas to check},HAS(@cell, "NIkki Pooler"))

Alternatively you could have a person column (filled with the person repeated) and use an @row reference instead and stack them and then use a filter on the COUNTIFS column to find anything over 1 (for example).

Apologies if this latter bit is a bit vague!

sjohanson

@Nick Korna

I will need to capture the # of times a persons name is listed in each row in the "HCS Project Listing" sheet for columns "Responsible" to "Data" using a formula in the "totals for dashboard" sheet.

Note: Now all columns in the "CHS Project Listing" sheet that have names are in "contact list" and "multi select"

Do you mind using my terminology? The "project" and "ABC" reference is throwing me off.

Thanks!!!

sjohanson

@Nick Korna If possible, open to a screen share if you are allowed to do so.

sjohanson

@Nick Korna Checking in on the above request

Nick Korna

Is the list of names going to change regularly, or would you be able to take a snapshot of it as is and go from there?

I ask because while it is possible to grab distinct names from a contacts list, it gets much more complicated where you have multiple in a cell (which can be seen in the screenshot).

The slightly cheesy way to get a list from a column would be to change the column (temporarily) into a dropdown one, at which point each value is listed (and you can copy/paste) before switching it back to contact list. You'd need to repeat this for each column and then remove duplicates (you can do this with a bit of sorting and a COUNTIF formula or just a sort and some conditional formatting in Excel).

If your contact line-up is ever changing, things are probably a bit more tricky!