# countifs but not both

Can someone help create a formula for counting how many times a contact name appears in two columns (one or the other or both), but only count it once if it appears in both. ? Thanks!

• ✭✭✭✭✭✭

In my screenshot, the bottom entry labeled the number 6 red stamp, it's finding my Contact in a multi-select Contact column with three selections made.

Maybe share some screenshots of your data with column names and your formula?

Regards,

Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭

You're using Contact type columns, yes? If so, this will work:

ContactName column = the contact you want to count.

ColumnA = first list of contact names

Column B = second list of contact name

=COUNTIFS(ColumnA:ColumnA, HAS(@cell, ContactName@row)) + COUNTIFS(ColumnB:ColumnB, HAS(@cell, ContactName@row)) - COUNTIFS(ColumnA:ColumnA, HAS(@cell, ContactName@row), ColumnB:ColumnB, HAS(@cell, ContactName@row))

In English, count the number of cells with this contact name from ColumnA, add that to the count of the number of cells with this contact name from ColumnB, and subtract from that the number of rows with the Contact name in both ColumnA and ColumnB.

From my test sheet, counting how many times I am listed in either column:

Regards,

Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• @Jeff Reisman Will this work if there's more than one contact in a cell? My #'s are still off. Also, I'm actually using a range from another smartsheet. Sorry - I'm forever a student I think!

• ✭✭✭✭✭✭

In my screenshot, the bottom entry labeled the number 6 red stamp, it's finding my Contact in a multi-select Contact column with three selections made.

Maybe share some screenshots of your data with column names and your formula?

Regards,

Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• YEP! This worked! (I missed a filter that was on) THANK YOU SOO MUCH!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!