COUNTIFS Across Different Ranges Returns #INVALID DATA TYPE

Summer

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.

Paul Newcome

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.

Summer

Edit to clarify: Unit Status is A, OR C, and Work Order Type is B

Paul Newcome

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.

Summer

Thank you, Paul!

Paul Newcome

Happy to help. 👍️