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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!