COUNTIFS based on two columns without duplicating
Hi all,
I have two Contact List columns where we track who is responsible for tasks.
I need to count for each row how many times a Contact is entered into EITHER of these columns. I don't want to count it TWICE if either of the columns have one of the values I'm looking for. These are the values I'm looking for in this example: AMT, CopyeditAMT, PadletAMT, LayoutAMT.
So, the results I'm looking for at each row shown below would be: 1, 1, 1, 1, 0. Again, I don't want it to count it twice if EITHER the Task Owner or Additional Resources has one of the conditions met.
Hope that makes sense.
Appreciate any formula advice I can get!
Thanks,
Jeana
Answers

Hi @Jeana
The way I would personally do this is to set up a helper column in this current source sheet. The helper column would flag if each row meets your criteria or not, say with a checkbox. Then your COUNT formula would look at this helper column and count the checkboxes. Does that make sense?
The helper checkbox formula would be a nested IF statement using OR, to check both cells for each of your criteria. Let me know if you'd like an example of this formula or sheet set up and I'm happy to help further!
Cheers,
Genevieve
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!