countifs but not both

Susan McM

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!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/320128#Comment_320128

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?

Jeff Reisman

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:

Susan McM

@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!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/320128#Comment_320128

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?

Susan McM

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