9

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. 

 

 

Comments

Andree_Stara

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

Andree_Stara

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, [email protected])

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}, "[email protected]")

 

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

 

=COUNTIFS({Email Address Column}, FIND("[email protected]", @cell) > 0)

In reply to by Paul Newcome

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?  

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.

Excellent! Happy to help! yes