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.

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!