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

DisnerdBree

New Member
Joined
Jul 15, 2019
Messages
2
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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,493
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Jul 15, 2019
Messages
2
perfect thank you, that's solved my problem!

I thought i was probably doing something stupid!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,493
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Glad to help, and welcome to the forum! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,655
Messages
5,524,135
Members
409,562
Latest member
meeranaskar

This Week's Hot Topics

Top