Contact formula in a metric sheet

Amy Bouhall

I have a sheet where the Assigned To can be multiple contacts. I can find the numbers for each. But it has been requested that I total each contact separately and then a row for Both. Below is the formula I am using. Any ideas how I can do it?

=COUNTIF({Global Communication Editorial R}, HAS(@cell, Label@row))

Katie only

Melissa only

Both Katie and Melissa

Other

None

M Underbrink

This may not be the "Correct" answer, but I've struggled to evaluate multi contact columns with formulas.

What I've done to circumvent it is create "helper" columns, that evaluate the different possibilities, and then use the helper columns to do reporting.

So you would have helper checkbox columns like this,

• Column Name - "Katie Only" | =IF([Assigned TO], "Katie", 1,0)
• Column Name - "Katie" | =IF([Assigned TO], "Melissa", 1,0)
• Column Name - "Both Katie and Melissa" | =IF([Assigned TO], CONTAINS(AND("Katie","Melissa)), 1,0)
• Column Name - "None" | =IF([Assigned TO], ISBLANK(@cell),1,0)
• Your other column would depend what kind of values you could have there (you could just take the above and put them in a bunch of nested IF() statements, and have a dropdown with the different values to put it into one column - just know it will be a super long formula. =IF([Assigned TO], "Katie", "Katie Only", IF([Assigned TO], ="Melissa", "Melissa Only", IF([Assigned TO], CONTAINS(AND("Katie","Melissa)), "both", IF([Assigned TO], ISBLANK(@cell), "none", "Other")))

Then your counting formula is a simple Countifs for each column,

• =COUNTIFS([Katie], 1) .... repeat for each column

Or if your helper column is a single column, you'd use the following, repeated for each value,

• =COUNTIFS([Helper Column], "Katie Only")

Amy Bouhall

Thanks for your easy solution. I was hoping to do it without a helper column, There may be more than two name, so hate to add to many columns to my table.

Ramzi K

@Amy Bouhall

Here's one way to do it:

In this example I am wanting to count the number of times things appear in the Contacts Selections (multiselect) column.

In the green section above, I have the things I want to count

1. Ramzi and Steve together
2. Ramzi alone
3. Steve alone

In the count column for both, I use this formula:

=COUNT(COLLECT([Contact Selections]:[Contact Selections], [Contact Selections]:[Contact Selections], HAS(@cell, Contact@row)))

for the other cells in the Count column, I use the same formula but subtract the value in the first cell:

=COUNT(COLLECT([Contact Selections]:[Contact Selections], [Contact Selections]:[Contact Selections], HAS(@cell, Contact@row))) - Count1

So in this example, Ramzi and Steve appear together twice and then Steve appears once by himself and Ramzi appears once by himself.

I hope this helps.

Cheers,

Ramzi