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
-
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
-
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!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives