SOLVED

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

Copper Contributor

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?

 

 

 

1 Reply
best response confirmed by Hans Vogelaar (MVP)
Solution

I figured out what is the issue...

 

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

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

I figured out what is the issue...

 

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

View solution in original post