COUNTIFs across multiple columns from another sheet

harrywhitehouse01

Hi all,

I'm currently struggling to execute a formula to count the number of times a contact appears across multiple columns from a sheet I'm referencing. I know I can just add two COUNTIFs statements for each column but this doesn't solve my problem of only wanting a contact to be contacted once for each row (eg if the contact appears in both column 1 and column 2 in the same row, I only want it to be counted once).

Thanks.

Jeff Reisman

@harrywhitehouse01

The COUNTIFS formula let's you specify multiple criteria, and will only a count a row as 1 if all criteria are met. In your case though, you're looking for "count a row once if this contact appears in either of these two columns," yes?

Questions: Do your Contact columns allow for multiple contacts to be selected in single cell?

Does the sheet where you're using this formula have a contact-type column that will contain the contact you're looking to count on the remote sheet?

If your remote contact columns are single select, and your local contact column is called 'Contact', then what you have to do here is count the rows where the contact is in BOTH columns, count the rows where it's in column 1 but NOT in column 2, and count the rows where it's in Column 2 but NOT in column 1, and add them all together.

=COUNTIFS({Remote Contact Col 1}, Contact@row, {Remote Contact Col 2}, Contact@row) + COUNTIFS({Remote Contact Col 1}, Contact@row, {Remote Contact Col 2}, @cell <> Contact@row) + COUNTIFS({Remote Contact Col 2}, Contact@row, {Remote Contact Col 1}, @cell <> Contact@row)

Jeff Reisman

@harrywhitehouse01

The COUNTIFS formula let's you specify multiple criteria, and will only a count a row as 1 if all criteria are met. In your case though, you're looking for "count a row once if this contact appears in either of these two columns," yes?

Questions: Do your Contact columns allow for multiple contacts to be selected in single cell?

Does the sheet where you're using this formula have a contact-type column that will contain the contact you're looking to count on the remote sheet?

If your remote contact columns are single select, and your local contact column is called 'Contact', then what you have to do here is count the rows where the contact is in BOTH columns, count the rows where it's in column 1 but NOT in column 2, and count the rows where it's in Column 2 but NOT in column 1, and add them all together.

=COUNTIFS({Remote Contact Col 1}, Contact@row, {Remote Contact Col 2}, Contact@row) + COUNTIFS({Remote Contact Col 1}, Contact@row, {Remote Contact Col 2}, @cell <> Contact@row) + COUNTIFS({Remote Contact Col 2}, Contact@row, {Remote Contact Col 1}, @cell <> Contact@row)