COUNTIF Formula for Contact Column

I have several tasks set up for team members using their email addresses in the Contact column. Some tasks have 1 member assigned and some have multiple. I have to run each person's workload and am trying to use a separate sheet that counts the number of times their email address is in the Contact column. However, the COUNTIF formula only counts them if there is only 1 person assigned in the Contact column. If there is more than 1 email address, the formula counts it as "0", even if their email address is in the cell. The goal is to have the workload calculate in "real time" in this sheet, using the COUNTIF formula, and be linked to the master sheet where the source data is located.

I've already submitted a product enhancement request, but need a workaround in the interim! Any ideas would be appreciated.

Find more posts tagged with

Contacts

Formulas

Support

Project Management

Enhancement Request

Comments

Andrée Starå

Hi Mel,

I think I have something that will help. I'll get back to this post a little later.

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Andrée Starå

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Paul Newcome

You should be able to incorporate the FIND function into the criteria of your COUNTIFS instead of looking for a match.

If your current formula looks like this which counts only if the email is the only one in the cell:

=COUNTIFS({Email Address Column}, "address@address.com")

then you would want to try something along the lines of this:

=COUNTIFS({Email Address Column}, FIND("address@address.com", @cell) > 0)

mel.greenspan

This doesn't seem to work when I'm referencing a Contact column in another sheet? It does work when I reference a range within the same sheet I'm doing the countifs in. Any ideas?

Paul Newcome

What is your current formula?

mel.greenspan

=COUNTIFS({Contact Column}, FIND("address@address.com", @cell) > 0)

Also tried this:

=COUNTIFS({Contact Column}, FIND("Person Name", @cell) > 0)

Paul Newcome

And you are following the correct steps to select the appropriate range for your cross sheet reference?

Are you typing in the correct email address? Spelling, punctuation, and upper/lower case all have to match.

It is working for me.

mel.greenspan

WORKING NOW!

Thanks!!!

Paul Newcome

Excellent! Happy to help!

ialarson

How do you make this work if you have other criteria that you want to be present, in addition to the "find" criteria. I'm trying to only count if all 4 of the criteria are present, (issue=project, progress = not full, status = yes and a particular person is in the Leader column) but it seems like it's not taking into account all the criteria, but I can't figure out how it's getting the 82 to figure out my error.

Here's my formula:

=COUNTIFS({Issue Type}, "Project", {Progress}, "<> Full", {Status}, "Yes", {Leader Responsible Party}, (FIND("Person 1", @cell) > 0))

I'm expecting 21 (hand count) and it's showing 82. I would expect 130 if I as looking for a full status.

Any suggestions?

Genevieve P.

Hi @ialarson

There are a few syntax elements here to adjust. The quotes for your second criteria should only be around "Full" and not the <> symbol. I would also personally use the HAS function to search for a person within the contact column if it's multi-select, like so:

=COUNTIFS({Issue Type}, "Project", {Progress}, <> "Full", {Status}, "Yes", {Leader Responsible Party}, HAS(@cell, "Person 1"))

Let me know if this returns the correct result!

Sign in to join the conversation!

Quick Links

Trending discussions

SUMIFS with date and category referencing another sheet
Hi - I have a cashflow chart that I want to pull expenses into by category and month. The expenses are located on another sheet with a dropdown list that is the same on the cashflow chart. I have tried this formula but it is not returning the results - just $0.00 =SUMIFS({Expenses Amount}, {Expenses Category},…
Help with IF FIND Formula
This discussion was created from comments split from: How to collect multiple Successor values?.
How to Have Children Rows Updated based on Data from Parent Row
I am setting up a sheet which will have many parent rows with many children connected to them. There is a column called "Escalated to AM?" which is a drop-down with the option to select "Escalated". There is another column called "Escalated?", which is a checkbox column. I want to add a formula to the "Escalated?" checkbox…