How to check for text as well as number using COUNTIFS

Jaime Holder

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.

Paul Newcome

Not a 3rd COUNTIFS. A second range/criteria set WITHIN the 2 already existing COUNTIFS.

Paul Newcome

Try just adding another range/criteria set to each of your already existing COUNTIFS.

Jaime Holder

https://community.smartsheet.com/discussion/comment/408396#Comment_408396

@Paul Newcome I believe I already did? If you look closely at the second screenshot, it shows a 3rd countifs statement with range for dept, which returns #unparseable...

EDIT - I should point out that my DEPT coulumn is a drop-down, but I have that counting on other sheets by itself so shouldn't be the issue (but maybe is the issue)

Paul Newcome

Not a 3rd COUNTIFS. A second range/criteria set WITHIN the 2 already existing COUNTIFS.

Jaime Holder

https://community.smartsheet.com/discussion/comment/408403#Comment_408403

@Paul Newcome - thank you so much, this was it!

JH

Paul Newcome

Happy to help. 👍️