COUNTIFS comparing two columns

Art Schneiderheinze

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

Kelly Moore

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

Toufong Vang

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.