COUNTIFs across multiple columns from another sheet

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.

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

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

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

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

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

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

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

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