Results 1 to 4 of 4

Thread: using "<>" (not equal to) as part of COUNTIFS
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,993
    Post Thanks / Like
    Mentioned
    53 Post(s)
    Tagged
    6 Thread(s)

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

    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.

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    perfect thank you, that's solved my problem!

    I thought i was probably doing something stupid!

  4. #4
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,993
    Post Thanks / Like
    Mentioned
    53 Post(s)
    Tagged
    6 Thread(s)

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

    Glad to help, and welcome to the forum!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •