COUNTIF Formula for Contact Column

mel.greenspan

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. 

 

 

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!