COUNTIF cells match in different sheets

SJM1609 · 12/15/23

I am struggling to get a count if cells match when referencing different sheets. I am working on "Sheet 0" and want to check how many cells in "Sheet 1" match cells in "Sheet 2" for one column.

Here is my formula: =COUNTIFS({Sheet 1 column}, ="{Sheet 2 column}")

have also tried without parenthesis. I currently get a 0 count which i know is false.

Any ideas would be appreciated.

Eric Law · 12/15/23

https://community.smartsheet.com/discussion/114424/countif-cells-match-in-different-sheets

So, what you are trying to do is a little bit... difficult. You cannot COUNTIF an array to an array as that is not a logic, but if it were you would only get the value of 1 if all objects in an array matches the other.

I am also confused about what it is you are trying to do. Are each items in a list unique? Are you trying to figure out if there are duplicates?

SJM1609 · 12/15/23

Hi @Eric Law ,

OK that makes sense.

I am attempting to see how many 'outcomes' have not changed after an 'appeal'. Which would mean the cells in the 'outcome' column in the first sheet should match the cells in the 'outcome' column in the 'appeal' sheet.

Hope this makes sense.

I also tried the opposite where cells do not match.

Eric Law · 12/15/23

https://community.smartsheet.com/discussion/comment/409819#Comment_409819

So, the best way is to probably CELL link Sheet 1 to Sheet 0. Then, in sheet 0, create a checkbox column that is =IF(CONTAINS(link@row, {sheet 2 column}, 1, 0) and it will checkbox all the ones that are in both. Hope that helps.

SJM1609 · 12/15/23

Thanks @Eric Law,

I've now linked both lists to sheet 0 and they are side by side. how can i count how many match? they are text and i cannot use 'Contains' as this produces some false figures.

Edit: Simple IF function worked to get count.

Thanks.

Eric Law · 12/15/23

https://community.smartsheet.com/discussion/comment/409838#Comment_409838

Since you've linked both, I will assume the column names to be Sheet 1 and Sheet 2. Create a Match checkbox column and just use =IF(COUNTIF([Sheet 2]:[Sheet 2], [Sheet 1]@row)>0, 1, 0) and that will checkbox any Sheet 1 items that match Sheet 2.

COUNTIF cells match in different sheets

Answers

Help Article Resources

Categories