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

  • Kelly Moore
    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
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!