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
-
Not a 3rd COUNTIFS. A second range/criteria set WITHIN the 2 already existing COUNTIFS.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Try just adding another range/criteria set to each of your already existing COUNTIFS.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@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)
-
Not a 3rd COUNTIFS. A second range/criteria set WITHIN the 2 already existing COUNTIFS.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome - thank you so much, this was it!
JH
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!