Thanks:  0
Likes:  0

# Thread: Negate counts in COUNTIFS?

1. ## 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. ## 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. ## 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.

4. ## 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)

5. ## 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

6. ## Re: Negate counts in COUNTIFS?

Originally Posted by KuraiChikara
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)

7. ## 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.

8. ## Re: Negate counts in COUNTIFS?

Originally Posted by Joyner
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.

9. ## Re: Negate counts in COUNTIFS?

Originally Posted by dreid1011
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. ## 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