COUNTIFS Across Different Ranges Returns #INVALID DATA TYPE
Hi Smartsheet Community,
I am stumped. I have a COUNTIFS formula that behaves as expected on my test sheet but returns an #INVALID DATA TYPE error when replicated on a actual sheet. The Column Types and formatting are the same on the test sheet and actual sheet. I would like to count the number of instances where Unit Status is A, and Work Order Type is B during a specific week.
=COUNTIFS({Week Of}, =[Date Value]$10, {Unit Status}, OR(@cell = [Value 1]$130, @cell = [Value 1]$127, @cell = [Value 1]$128, {WO Type}, @cell = [Value 1]$115))
Reference sheet: Week Of = Date Column, Unit Status = Dropdown (Single Select), WO Type = Dropdown (Single Select)
Formula Sheet: Date Value 10 = Date Column, Value 1 = Text/Number Column
Formula is being placed in a Text/Number Column as the result should be a numerical value.
Sometimes the formula will show the Invalid data type error right from the start, other times it will show 0 until I enter the relevant WO and Unit info on the reference sheet and then will give me the error.
I feel like I'm missing something small but can't pinpoint it.
Best Answer
-
You have a misplaced closing parenthesis. Move one from the very end to after [Value 1]$128 so that you close out the OR statement before moving on with the rest of the 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
-
Edit to clarify: Unit Status is A, OR C, and Work Order Type is B
-
You have a misplaced closing parenthesis. Move one from the very end to after [Value 1]$128 so that you close out the OR statement before moving on with the rest of the 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!
-
Thank you, Paul!
-
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
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!