COUNTIFS formula issue. Help Please?

Sam Nies

Greetings!

I am trying to count the number of times a contact appears in a range on a sheet when certain conditions apply. I am trying to make this a column formula instead of a row formula so I don't have to input every individual's name into a formula for what will end up being 20 columns of counting.

The range to search for the contact is five columns, three of which are single contact columns and two that are multiple contact columns. Here is what I have that I feel should work but is not.

=COUNTIFS({2023 BAY Schedule Range 2}, HAS(@cell, [Employee Name]@row), {2023 BAY Schedule Range 3}, FIND("Open", @cell) > 0, {2023 BAY Schedule Range 1}, FIND("Paddle", @cell) > 0))

Thanks for any and all help!

Sam

Jeff Reisman

@Sam Nies Is there some reason that these NEED to be contact columns? Like are you sending automated alerts or running Approval processes on them?

You may be better off just using names from a regular dropdown list. You can create a lookup sheet for Employees that matches their name to a Contact, and use that to bring the contact values into your sheet if you need to send emails to them. You could also use Assign People automation to set values in a contact list column automatically:

Trigger: When Guide 1 column changes

Condition: When guide 1 = Cecily's name

Action: Assign Cecily's contact record in Contact list column

Unselecting 'Replace existing values in multi-select column' will add Cecily to any contacts already in the cell, if desired.

Melissa Boehl

Please share a screenshot of the sheet you are referencing. You can black out the sheet data.

Sam Nies

Hi Melissa, sure.

Jeff Reisman

@Sam Nies What error message are you getting? Can you match the ranges with the columns they are referencing?

(Note: As a best practice, when I create range references, I normally replace "Range #" with a hint about what column the range covers. So instead of {2023 BAY Schedule Range 3}, I would call that {2023 BAY Schedule Dep Status})

Sam Nies

@Jeff Reisman With this formula I am seeing an UNPARESABLE error. I do always label my ranges in formulas when they're complete. Seeing as how this is work in flight and maybe not even possible, I haven't shined it up yet

Jeff Reisman

@Sam Nies First thing I would do with an unparseable error is to try the COUNTIFS with each range/criteria combination by itself. That will identify or rule out any criteria or criteria syntax issues. If they all work individually, then the issue may be something with other punctuation in the formula.

Also try to simplify criteria wherever possible. If you're looking for the word 'Open' in a column, and a cell saying 'Open' would only have 'Open' and nothing else in the cell, just keep the criteria simple:

{2023 BAY Schedule Range 3}, "Open"

If the text value you want is just part of a cell, you can use CONTAINS to find that string:

{2023 BAY Schedule Range 1}, CONTAINS("Paddle", @cell)

Regarding Contact List columns 0 these can be extremely finicky when it comes to matching lookup values. If I have a contact called Test6 with an email TestUser6@test.com, HAS(@cell, "Test6") will match, but HAS(@cell, "TestUser6@test.com") will not.

Sam Nies

@Jeff Reisman Thank you for the advice on cleaning up the formula losing the FIND. The formula works for the initial criteria of counting the instances of "Paddle" and of "Open" but gets busted when I get to the contact columns.

It works when selecting just one of the columns but not the range of all five columns. I have tried counting each column separately but when I do Guide 1 and Guide 2 columns it gives a 0.

The same for when I select either of the last two columns that are multiple contact columns. If I have Cecily assigned singularly in one of those columns and I have a range that is searching for just that column, the formula works. As soon as I add another contact, it gives a 0.

Thank you again for taking the time to review all this!

Jeff Reisman

@Sam Nies Is there some reason that these NEED to be contact columns? Like are you sending automated alerts or running Approval processes on them?

You may be better off just using names from a regular dropdown list. You can create a lookup sheet for Employees that matches their name to a Contact, and use that to bring the contact values into your sheet if you need to send emails to them. You could also use Assign People automation to set values in a contact list column automatically:

Trigger: When Guide 1 column changes

Condition: When guide 1 = Cecily's name

Action: Assign Cecily's contact record in Contact list column

Unselecting 'Replace existing values in multi-select column' will add Cecily to any contacts already in the cell, if desired.

Sam Nies

@Jeff Reisman Thank you again for the help. I actually pieced it together with help from another post's answer that you gave. I made a column that summed up all the contact's names in any given assigned row, then I made a new column on my metrics sheet that wasn't a contact column but just text copied over from the contact. Then I used that as:

=COUNTIFS({Departure Status}, "Open", {Trip ID}, "Paddle", {Calendar}, CONTAINS(Contact@row, @cell))

The finished formula works!

Thank you again for your help

Jeff Reisman

@Sam Nies Excellent! Glad you figured it out.