Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Negate counts in COUNTIFS?

  1. #1
    Board Regular
    Join Date
    Nov 2016
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Negate counts in COUNTIFS?

    I was wondering if you can negate counts in a COUNTIFS?
    Example:

    =COUNTIFS(A:A,A2,B:B,B2,C:C,C2)
    My formula is looking in all of column A (or Agent) with "John" as the Criteria.
    My formula is looking in all of column B (or Day) with "Monday" as the Criteria.
    My formula is looking in all of column C (or Time) with "18:00" as the Criteria

    Outcome:
    My formula works, it finds 3 instances of John, Monday and 18:00. However, what I would like is for the COUNTIFS to search an entire column like I have but if there are duplicates to some how have an end calculation of 1.


    I would like this outcome with a result of 3:
    =COUNTIFS(A:A,A2,B:B,B2,C:C,C2)
    Agent Day Time Formula
    John Monday 18:00 3
    John Monday 18:00
    John Monday 18:00


    To look like this with a result of 1, with a COUNTIFS function if possible:
    =COUNTIFS(A:A,A2,B:B,B2,C:C,C2)???
    Agent Day Time Formula
    John Monday 18:00 1
    John Monday 18:00
    John Monday 18:00

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,178
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Negate counts in COUNTIFS?

    Your question is not clear (at least for me)

    What would be the desired result with a data sample like this?

    A
    B
    C
    1
    Name
    Day
    Time
    2
    John
    Monday
    18:00
    3
    John
    Monday
    18:00
    4
    John
    Monday
    18:00
    5
    John
    Monday
    19:00
    6
    John
    Monday
    19:00
    7
    John
    Tuesday
    18:00
    8
    John
    Tuesday
    18:00
    9
    John
    Tuesday
    19:00


    M.

  3. #3
    Board Regular
    Join Date
    Nov 2016
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Negate counts in COUNTIFS?

    Essentially I just want the COUNTIFS and maybe additional functions thrown in, to come to a final calculation of 1 regardless of duplicates.

    In your example with this formula: =COUNTIFS($B:$B,B4,$C:$C,C4,$D:$D,D4)
    A B C D
    1 Name Day Time Formula
    2 John Monday 18:00 3
    3 John Monday 18:00 3
    4 John Monday 18:00 3
    5 John Monday 19:00 2
    6 John Monday 19:00 2
    7 John Tuesday 18:00 2
    8 John Tuesday 18:00 2
    9 John Tuesday 19:00 1


    John, Monday, 18:00 from my formula would show 3.
    John, Monday, 19:00 from my formula would show 2.
    John, Tuesday, 18:00 from my formula would show 2
    John, Tuesday. 19:00 from my formula would show 1.

    My overall goal would be for the COUNTIFS to return a 1 regardless of duplicates found.
    Last edited by KuraiChikara; Jul 26th, 2018 at 12:01 PM.

  4. #4
    Board Regular
    Join Date
    Nov 2005
    Location
    Clearwater, Florida
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Negate counts in COUNTIFS?

    So do you want to just match on "John" that should return one?

    Maybe:

    =IF(ISNUMBER(MATCH("John",$A$1:$A$9,0)),1,0)
    Last edited by Joyner; Jul 26th, 2018 at 12:07 PM.
    forum use guidelines; forum rules; terms of use; FAQs Use code tags [ Code ] your code here [ /Code ] Try searching for your answer first, see how

    Work - 32 bit Office 2016 Win10 .... Home - Office 365 Win10

    I solve for X but don't know Y


  5. #5
    Board Regular
    Join Date
    Nov 2016
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Negate counts in COUNTIFS?

    That's good but my issue is the days and time frame are also import. Think of John like a supervisor so his name could be duplicated 15 times or more based on people that work under him. Think of the Day and Time to be coaching times. A match with 15 instances would return 1, because of 1 supervisor named John.

    However, back to your original example grid.
    You listed 4 time frame examples with some that have duplicates but overall 4 unique time frames.

    John, Monday, 1800
    John, Monday, 1900
    John, Tuesday, 1800
    John, Tuesday, 1900

    There might be 3 John, Monday, 1800 listed but overall that is still just 1 time frame, that's what I'm trying to accomplish. Perform the countifs, duplicates might be found, negate the duplicates to represent what was found as a 1 instead 2, 3, 5, 8 or whatever amount of duplicates could be listed.




    A B C D
    1 Name Day Time Formula
    2 John Monday 18:00 3
    3 John Monday 18:00 3
    4 John Monday 18:00 3
    5 John Monday 19:00 2
    6 John Monday 19:00 2
    7 John Tuesday 18:00 2
    8 John Tuesday 18:00 2
    9 John Tuesday 19:00 1
    Last edited by KuraiChikara; Jul 26th, 2018 at 12:35 PM.

  6. #6
    Board Regular
    Join Date
    Nov 2005
    Location
    Clearwater, Florida
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Negate counts in COUNTIFS?

    Quote Originally Posted by KuraiChikara View Post
    That's good but my issue is the days and time frame are also import. Think of John like a supervisor so his name could be duplicated 15 times or more based on people that work under him. Think of the Day and Time to be coaching times. A match with 15 instances would return 1, because of 1 supervisor named John.

    There might be 3 John, Monday, 1800 listed but overall that is still just 1 time frame, that's what I'm trying to accomplish. Perform the countifs, duplicates might be found, negate the duplicates to represent what was found as a 1 instead 2, 3, 5, 8 or whatever amount of duplicates could be listed.
    This still is not clear to me, can you just post what your expected results should be? Or is it what you show above?


    Do you want this:

    =IF(COUNTIFS($B:$B,B4,$C:$C,C4,$D:$D,D4)>0,1,0)
    Last edited by Joyner; Jul 26th, 2018 at 12:49 PM.
    forum use guidelines; forum rules; terms of use; FAQs Use code tags [ Code ] your code here [ /Code ] Try searching for your answer first, see how

    Work - 32 bit Office 2016 Win10 .... Home - Office 365 Win10

    I solve for X but don't know Y


  7. #7
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,178
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Negate counts in COUNTIFS?

    Please, could you tell us the desired results in D2:D9?


    A
    B
    C
    D
    1
    Name
    Day
    Time
    Formula
    2
    John
    Monday
    18:00
    ?
    3
    John
    Monday
    18:00
    ?
    4
    John
    Monday
    18:00
    ?
    5
    John
    Monday
    19:00
    ?
    6
    John
    Monday
    19:00
    ?
    7
    John
    Tuesday
    18:00
    ?
    8
    John
    Tuesday
    18:00
    ?
    9
    John
    Tuesday
    19:00
    ?


    Or do you want a formula in just one cell that performs a count unique? If so, tell us the expected result considering the data sample above.

    M.
    Last edited by Marcelo Branco; Jul 26th, 2018 at 12:50 PM.

  8. #8
    Board Regular
    Join Date
    Jun 2015
    Location
    Tillamook, OR
    Posts
    2,111
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Negate counts in COUNTIFS?

    Quote Originally Posted by Joyner View Post
    This still is not clear to me, can you just post what your expected results should be? Or is it what you show above?


    Do you want this:

    =IF(COUNTIFS($B:$B,B4,$C:$C,C4,$D:$D,D4)>0,1,0)
    OP is looking for unique count based on the 3 criteria. If the list contains two or more entries with "John" "Monday" "18:00", then count them all as a single entry.
    Win 7:MS Excel 2010

    Excel Worksheet Functions (TechOnTheNet)
    Excel Worksheet Functions (ExcelJet) Excel Formulas (ExcelJet)

    Post a copy of your data with one of the following methods:
    Excel Jeanie, MrExcel HTML Maker, Tableizer!

  9. #9
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,178
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Negate counts in COUNTIFS?

    Quote Originally Posted by dreid1011 View Post
    OP is looking for unique count based on the 3 criteria. If the list contains two or more entries with "John" "Monday" "18:00", then count them all as a single entry.
    If so, simply put 1 on each row...
    Does it make sense?

    M.

  10. #10
    Board Regular
    Join Date
    Nov 2016
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Negate counts in COUNTIFS?

    This works, thank you. Your formula puts a 1 if the count is greater than 0 and the 1 entry date you had "John/Tuesday/1900" is left as a 1.
    =IF(COUNTIFS($B:$B,B4,$C:$C,C4,$D:$D,D4)>0,1,0)

    A B C D
    1 Name Day Time Formula
    2 John Monday 18:00 1
    3 John Monday 18:00 1
    4 John Monday 18:00 1
    5 John Monday 19:00 1
    6 John Monday 19:00 1
    7 John Tuesday 18:00 1
    8 John Tuesday 18:00 1
    9 John Tuesday 19:00 1

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
  •