Forum Discussion

KnJ_C's avatar
KnJ_C
Copper Contributor
Jan 20, 2023
Solved

CountIfs returning "A value used in this formula is of wrong data type"

This is my formula

 

COUNTIFS(OneNoteLeaveTypes_2023,"Annual",OneNoteNotes_2023,"<>Tentative",OneNoteYear,B4)

 

each criteria set individually works as expected.

 

COUNTIFS(OneNoteLeaveTypes_2023,"Annual",OneNoteNotes_2023,"<>Tentative")

 

works as expected.

 

It is just when I add the third criteria (i.e. "OneNoteYear,B4") is when I get the formula error "A value used in this formula is of wrong data type.

 

I have tried to not use "named cells" and have used hard coded text for the "B4" cell reference but nothing seems to work.

I have tried to force cell format types from "general" to "text".

 

It feels like this formula cannot handle the third criteria (i.e. it is too complex).  It feels like a bug in Excel.

 

Anyone have any clue as to what I am possibly missing?

 

 

 

  • I figured out what is the issue...

     

    the "countifs" formula does not like ranges (named cells or not) of different lengths.

  • KnJ_C's avatar
    KnJ_C
    Copper Contributor

    I figured out what is the issue...

     

    the "countifs" formula does not like ranges (named cells or not) of different lengths.

Resources