COUNTIFS based on two columns without duplicating

Jeana

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

Genevieve P.

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