Value used in formula is wrong data type

[Hooch]

Hi all,

I am trying to compare a range against a single value to see if it exists and return a single Boolean T/F result. The formula is:

=IF(COUNTIF(RANGE,CRITERIA)>0, "TRUE", "FALSE")

The problem I'm having is the way I supply the RANGE to COUNTIF will be fine if I explicitly highlight a range on a worksheet, but if I supply the formula that generated that range directly to COUNTIF I get a data value error.

So the data looks like this in a table named 'Booked':

Name	Date
A	02/05/2023​
A	27/01/2023​
A	26/01/2023​
A	05/06/2023​
A	12/06/2023​
A	24/03/2023​
A	13/06/2023​
D	27/02/2023​
P	30/01/2023​
P	05/06/2023​
P	25/09/2023​
Y	05/01/2023​
Y	27/03/2023​

I generate a range from this data matching based on 'name' i.e. =IF(Booked[Name]="A", Booked[Date], 0)

Which results in this range:

45048​

44953​

44952​

45082​

45089​

45009​

45090​

0​

0​

0​

0​

0​

0​

Now if I insert this data into D1 by putting the IF formula there, I can access this range in COUNTIF no problem i.e. =COUNTIF(D1#, 45048) gives me a result of 1

However, if I directly insert the IF statement that generated that range into my COUNTIF formula I get data type errors i.e. =COUNTIF(IF(Booked[Name]="A", Booked[Date], 0), 45048)
and instead of getting the result of 1, which is my intention I get this:

#VALUE!​

#VALUE!​

#VALUE!​

#VALUE!​

#VALUE!​

#VALUE!​

#VALUE!​

#VALUE!​

#VALUE!​

#VALUE!​

#VALUE!​

#VALUE!​

#VALUE!​

Does anyone know why a data type error would creep in here and if so how can I resolve this to get my intended output of "1" in D1 instead of a range of "#VALUE!" in D1:D13?

Sorry if I've formatted this post badly or left something out, please let me know, I'm new to excel and this forum so apologies.

 

[RoryA]

Your formula produces an array, not a range, and COUNTIF will not work with arrays. You could use SUMPRODUCT instead:

=SUMPRODUCT((Booked[Name]="A")*(Booked[Date]=45048))

 

[Fluff]

The xxIFS function need a range, not an array.
Try

=COUNTIFS(Booked[Name],"A", Booked[Date], 45048)

 

[Hooch]

Thank you both so much and Fluff, the function you supplied in your solution is actually a much better way of doing what I'm trying to do, thanks.

 

[Fluff]

Glad we could help & thanks for the feedback.