Negate counts in COUNTIFS?

KuraiChikara

Board Regular
Joined
Nov 16, 2016
Messages
111
Office Version
  1. 2013
Platform
  1. Windows
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)
AgentDayTimeFormula
JohnMonday18:003
JohnMonday18:00
JohnMonday18:00

<tbody>
</tbody>


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)???
AgentDayTimeFormula
JohnMonday18:001
JohnMonday18:00
JohnMonday18:00

<tbody>
</tbody>
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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​

<tbody>
</tbody>


M.
 
Upvote 0
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 TimeFormula
2 John Monday 18:003
3 John Monday 18:003
4 John Monday 18:003
5 John Monday 19:002
6 John Monday 19:002
7 John Tuesday 18:002
8 John Tuesday 18:002
9 John Tuesday 19:001

<tbody>
</tbody>


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:
Upvote 0
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:
Upvote 0
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.




ABCD
1NameDayTimeFormula
2JohnMonday18:003
3JohnMonday18:003
4JohnMonday18:003
5JohnMonday19:002
6JohnMonday19:002
7JohnTuesday18:002
8JohnTuesday18:002
9JohnTuesday19:001

<tbody>
</tbody>
 
Last edited:
Upvote 0
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:
Upvote 0
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:
Upvote 0
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.
 
Upvote 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.

If so, simply put 1 on each row...
Does it make sense?

M.
 
Upvote 0
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

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top