Results 1 to 5 of 5

Thread: Countifs with Or tidy-up
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2018
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Countifs with Or tidy-up

    Hi,

    Can anyone help condense this please?

    Code:
    =COUNTIFS(DatesWork!$C$2:$C$3000,"GATED",DatesWork!$G$2:$G$3000,"01/01/1900")+COUNTIFS(DatesWork!$C$2:$C$3000,"GATED",DatesWork!$G$2:$G$3000,"#")+COUNTIFS(DatesWork!$C$2:$C$3000,"GATED",DatesWork!$G$2:$G$3000,"No Date")
    Thanks

  2. #2
    Board Regular JustynaMK's Avatar
    Join Date
    Aug 2016
    Location
    London, UK
    Posts
    436
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs with Or tidy-up

    Try something like that:

    Code:
    =SUMPRODUCT(--($C$2:$C$3000="GATED"),($G$2:$G$3000=1)+($G$2:$G$3000="#")+($G$2:$G$3000="No Date"))
    Please note that I replaced "01/01/1900" with "1", as this is the first registered date in the system.

  3. #3
    Board Regular
    Join Date
    Sep 2018
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs with Or tidy-up

    Thank you, that did the trick 😀

  4. #4
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Countifs with Or tidy-up

    Hi,

    Try also this:

    =SUM(COUNTIFS(DatesWork!$C$2:$C$3000,"GATED",DatesWork!$G$2:$G$3000,{"01/01/1900","#","No Date"}))

  5. #5
    Board Regular JustynaMK's Avatar
    Join Date
    Aug 2016
    Location
    London, UK
    Posts
    436
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs with Or tidy-up

    You're welcome jtakw's solution will also work perfectly fine. Cheers.

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
  •