using "<>" (not equal to) as part of COUNTIFS

[DisnerdBree]

I'm having a very specific problem and I'm hoping somebody here can put me out of my misery!!

I have this COUNTIFS in place which does exactly what it needs to...

=COUNTIFS(C$5:C$66,"AM",D$5:D$66,"Please Select")

which is counting on a team calendar to show how many AM slots (in column C) are unassigned - which is shown by the corresponding drop down in column D still being "please select"

To help aid with an overall summary page, I need to add an additional criteria so that it only counts the above where a trainers name has been selected (cell C4 in above screenshot)- aka. it doesn't show as unassigned slots where there isn't a trainer selected!

Here is where I have an issue - every time I add the additional criteria - C4,"<>Select trainer" - it brings back a #VALUE ! error

I've checked the criteria as a stand alone =COUNTIFS(C4,"<>Select trainer") and it works as expected (see screenshot below)

But when I try and combine it all into one COUNTIFS formula =COUNTIFS(C$5:C$66,"AM",D$5:D$66,"Please Select",C4,"<>Select trainer") it brings back the #VALUE ! error (below screenshot). I've tried building it in every possible order - I've googled the life out of it but all I'm finding is to use =COUNTIFS(Range,"<>Criteria")

I can only assume that, for whatever reason, Excel doesn't like the composition of the formula when combining the two types of function (a mix of criteria equals and criteria does not equal)

Can anyone help?

PLEASE NOTE: I'm building this entirely as a favour for a friend, and have been asked to idiot proof it so that it can't be broken once they hand it off; the various teams that will use it all have a different number of team members and I have been told they can't be trusted to just delete any leftover trainer columns - otherwise I would just do that

 

[RoryA]

All the ranges in a COUNTIFS formula need to be the same size. All you really need is to add an IF clause:

=IF(C4<>"Select trainer",COUNTIFS(C$5:C$66,"AM",D$5:D$66,"Please Select"),"")

for example.

 

[DisnerdBree]

perfect thank you, that's solved my problem!

I thought i was probably doing something stupid!

 

[RoryA]

Glad to help, and welcome to the forum!