COUNTIFS comparing two columns
How do I format criterion in a COUNTIFS comparing the values in two columns?
EX: Column 1: Names of Designers (LXD), Column 2: Names of Course Builders (CB)
I want to count the number of instances when the Name of the customer doesn't match the name of the client per row.
ROW 1: | John D | Mary A
ROW 2: | John D | John D
ROW 3: | Paul R | Mike M
ROW 4: | Paul R | Paul R
The result would be 2 since there are two rows where LXD doesn't match CB (rows 1 and 3).
Answers
-
Hello @aschneiderheinze74206
One approach is to add a helper column (a checkbox column will do) to evaluate the two columns, row by row. =IF([Customer column]@row <> [Client column]@row, 1)
The above says if customer column doesn't equal the client column, check the box.
Then, using a Sheet Summary formula or other, COUNTIFS([helper checkbox]:[helper checkbox], 1)
Be sure to replace the column names in the formula above with your actual column names.
Will this work for you?
Kelly
-
There are a several approaches to achieve what you're trying to do. In addition to KDM's, you can try...
=COUNTIFS([Column 1]:[Column 1],<>"",[Column 2]:[Column 2], [Column 2]@row <> [Column 1]@row)
With this formula, you're counting everything in Column 1 that isn't blank BUT only when what's in Column 2 is not equal to it.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!