How to check for text as well as number using COUNTIFS


Hello - below is a screenshot of my worksheet, I have some success with our formula so far, but need it to check for the department as well as the number check.

For reference, the formula so far is

=IF(COUNTIFS({FY24_SO#}, @cell = [ORDER#]@row) + COUNTIFS({FY24_WO#}, @cell = [ORDER#]@row) = 0, "none", "DEFECT!")

this checks two columns on a referenced sheet and returns basic true/false if a number matches; conditional formatting highlights where true. I found this formula example on another community post and applied it here to some success.

I need to add the department column to the formula - i.e. I need the true/false statement to match a 3rd (department) column on the reference sheet, and then return the true/false value.

the department will change over time (to either FAB or WELD) if I can get this to work - so logically I am asking

=IF(check ref sheet for SO number/WO number) AND (check if department is the same) THEN (return yes/no)

it's likely something simple that i just can't see.

any help is appreciated, TIA

PS - I have already tried a few things, but none seem to work (I get INVALID and UNPARSEABLE typically), and DCHLPR is for an automation I'm working on.

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!