Negate counts in COUNTIFS?

[KuraiChikara]

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)
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Agent[/TD]
[TD]Day[/TD]
[TD]Time[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Monday[/TD]
[TD]18:00[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Monday[/TD]
[TD]18:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Monday[/TD]
[TD]18:00[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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)???
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Agent[/TD]
[TD]Day[/TD]
[TD]Time[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Monday[/TD]
[TD]18:00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Monday[/TD]
[TD]18:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Monday[/TD]
[TD]18:00[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Your question is not clear (at least for me)

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

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Name​

[/TD]
[TD]

Day​

[/TD]
[TD]

Time​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

John​

[/TD]
[TD]

Monday​

[/TD]
[TD]

18:00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

John​

[/TD]
[TD]

Monday​

[/TD]
[TD]

18:00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

John​

[/TD]
[TD]

Monday​

[/TD]
[TD]

18:00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

John​

[/TD]
[TD]

Monday​

[/TD]
[TD]

19:00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

John​

[/TD]
[TD]

Monday​

[/TD]
[TD]

19:00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

John​

[/TD]
[TD]

Tuesday​

[/TD]
[TD]

18:00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

John​

[/TD]
[TD]

Tuesday​

[/TD]
[TD]

18:00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

John​

[/TD]
[TD]

Tuesday​

[/TD]
[TD]

19:00​

[/TD]
[/TR]
</tbody>[/TABLE]


M.

 

[KuraiChikara]

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)
[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl72, width: 64"][/TD]
[TD="class: xl68, width: 64"] A[/TD]
[TD="class: xl68, width: 64"] B[/TD]
[TD="class: xl68, width: 64"] C[/TD]
[TD="class: xl68, width: 64"] D[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD="class: xl65, width: 64"] 1[/TD]
[TD="class: xl66, width: 64"] Name[/TD]
[TD="class: xl66, width: 64"] Day[/TD]
[TD="class: xl66, width: 64"] Time[/TD]
[TD="class: xl70, width: 64"]Formula[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"] 2[/TD]
[TD="class: xl66, width: 64"] John[/TD]
[TD="class: xl66, width: 64"] Monday[/TD]
[TD="class: xl67, width: 64"] 18:00[/TD]
[TD="class: xl71, width: 64"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"] 3[/TD]
[TD="class: xl66, width: 64"] John[/TD]
[TD="class: xl66, width: 64"] Monday[/TD]
[TD="class: xl67, width: 64"] 18:00[/TD]
[TD="class: xl71, width: 64"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"] 4[/TD]
[TD="class: xl66, width: 64"] John[/TD]
[TD="class: xl66, width: 64"] Monday[/TD]
[TD="class: xl67, width: 64"] 18:00[/TD]
[TD="class: xl71, width: 64"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"] 5[/TD]
[TD="class: xl66, width: 64"] John[/TD]
[TD="class: xl66, width: 64"] Monday[/TD]
[TD="class: xl67, width: 64"] 19:00[/TD]
[TD="class: xl71, width: 64"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"] 6[/TD]
[TD="class: xl66, width: 64"] John[/TD]
[TD="class: xl66, width: 64"] Monday[/TD]
[TD="class: xl67, width: 64"] 19:00[/TD]
[TD="class: xl71, width: 64"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"] 7[/TD]
[TD="class: xl66, width: 64"] John[/TD]
[TD="class: xl66, width: 64"] Tuesday[/TD]
[TD="class: xl67, width: 64"] 18:00[/TD]
[TD="class: xl71, width: 64"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"] 8[/TD]
[TD="class: xl66, width: 64"] John[/TD]
[TD="class: xl66, width: 64"] Tuesday[/TD]
[TD="class: xl67, width: 64"] 18:00[/TD]
[TD="class: xl71, width: 64"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"] 9[/TD]
[TD="class: xl66, width: 64"] John[/TD]
[TD="class: xl66, width: 64"] Tuesday[/TD]
[TD="class: xl67, width: 64"] 19:00[/TD]
[TD="class: xl71, width: 64"]1[/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[Joyner]

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)

 

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

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.




[TABLE="class: cms_table"]
<tbody>[TR]
[TD="class: cms_table_xl68"]A[/TD]
[TD="class: cms_table_xl68, width: 64"]B[/TD]
[TD="class: cms_table_xl68, width: 64"]C[/TD]
[TD="class: cms_table_xl68, width: 64"]D[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD="class: cms_table_xl65, width: 64"]1[/TD]
[TD="class: cms_table_xl66, width: 64"]Name[/TD]
[TD="class: cms_table_xl66, width: 64"]Day[/TD]
[TD="class: cms_table_xl66, width: 64"]Time[/TD]
[TD="class: cms_table_xl70, width: 64"]Formula[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, width: 64"]2[/TD]
[TD="class: cms_table_xl66, width: 64"]John[/TD]
[TD="class: cms_table_xl66, width: 64"]Monday[/TD]
[TD="class: cms_table_xl67, width: 64"]18:00[/TD]
[TD="class: cms_table_xl71, width: 64"]3[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, width: 64"]3[/TD]
[TD="class: cms_table_xl66, width: 64"]John[/TD]
[TD="class: cms_table_xl66, width: 64"]Monday[/TD]
[TD="class: cms_table_xl67, width: 64"]18:00[/TD]
[TD="class: cms_table_xl71, width: 64"]3[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, width: 64"]4[/TD]
[TD="class: cms_table_xl66, width: 64"]John[/TD]
[TD="class: cms_table_xl66, width: 64"]Monday[/TD]
[TD="class: cms_table_xl67, width: 64"]18:00[/TD]
[TD="class: cms_table_xl71, width: 64"]3[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, width: 64"]5[/TD]
[TD="class: cms_table_xl66, width: 64"]John[/TD]
[TD="class: cms_table_xl66, width: 64"]Monday[/TD]
[TD="class: cms_table_xl67, width: 64"]19:00[/TD]
[TD="class: cms_table_xl71, width: 64"]2[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, width: 64"]6[/TD]
[TD="class: cms_table_xl66, width: 64"]John[/TD]
[TD="class: cms_table_xl66, width: 64"]Monday[/TD]
[TD="class: cms_table_xl67, width: 64"]19:00[/TD]
[TD="class: cms_table_xl71, width: 64"]2[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, width: 64"]7[/TD]
[TD="class: cms_table_xl66, width: 64"]John[/TD]
[TD="class: cms_table_xl66, width: 64"]Tuesday[/TD]
[TD="class: cms_table_xl67, width: 64"]18:00[/TD]
[TD="class: cms_table_xl71, width: 64"]2[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, width: 64"]8[/TD]
[TD="class: cms_table_xl66, width: 64"]John[/TD]
[TD="class: cms_table_xl66, width: 64"]Tuesday[/TD]
[TD="class: cms_table_xl67, width: 64"]18:00[/TD]
[TD="class: cms_table_xl71, width: 64"]2[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, width: 64"]9[/TD]
[TD="class: cms_table_xl66, width: 64"]John[/TD]
[TD="class: cms_table_xl66, width: 64"]Tuesday[/TD]
[TD="class: cms_table_xl67, width: 64"]19:00[/TD]
[TD="class: cms_table_xl71, width: 64"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joyner]

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)

 

[Marcelo Branco]

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


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Name​

[/td][td]

Day​

[/td][td]

Time​

[/td][td]

Formula​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

John​

[/td][td]

Monday​

[/td][td]

18:00​

[/td][td]

?​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

John​

[/td][td]

Monday​

[/td][td]

18:00​

[/td][td]

?​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

John​

[/td][td]

Monday​

[/td][td]

18:00​

[/td][td]

?​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

John​

[/td][td]

Monday​

[/td][td]

19:00​

[/td][td]

?​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

John​

[/td][td]

Monday​

[/td][td]

19:00​

[/td][td]

?​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

John​

[/td][td]

Tuesday​

[/td][td]

18:00​

[/td][td]

?​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

John​

[/td][td]

Tuesday​

[/td][td]

18:00​

[/td][td]

?​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

John​

[/td][td]

Tuesday​

[/td][td]

19:00​

[/td][td]

?​

[/td][/tr]
[/table]

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.

 

[dreid1011]

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.

 

[Marcelo Branco]

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.

 

[KuraiChikara]

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)

[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl73, width: 64"] [/TD]
[TD="class: xl69, width: 64"] A
[/TD]
[TD="class: xl69, width: 64"] B
[/TD]
[TD="class: xl69, width: 64"] C
[/TD]
[TD="class: xl69, width: 64"] D
[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD="class: xl66, width: 64"] 1
[/TD]
[TD="class: xl67, width: 64"] Name
[/TD]
[TD="class: xl67, width: 64"] Day
[/TD]
[TD="class: xl67, width: 64"] Time
[/TD]
[TD="class: xl71, width: 64"]Formula[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"] 2
[/TD]
[TD="class: xl67, width: 64"] John
[/TD]
[TD="class: xl67, width: 64"] Monday
[/TD]
[TD="class: xl68, width: 64"] 18:00
[/TD]
[TD="class: xl72, width: 64"]1[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"] 3
[/TD]
[TD="class: xl67, width: 64"] John
[/TD]
[TD="class: xl67, width: 64"] Monday
[/TD]
[TD="class: xl68, width: 64"] 18:00
[/TD]
[TD="class: xl72, width: 64"]1[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"] 4
[/TD]
[TD="class: xl67, width: 64"] John
[/TD]
[TD="class: xl67, width: 64"] Monday
[/TD]
[TD="class: xl68, width: 64"] 18:00
[/TD]
[TD="class: xl72, width: 64"]1[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"] 5
[/TD]
[TD="class: xl67, width: 64"] John
[/TD]
[TD="class: xl67, width: 64"] Monday
[/TD]
[TD="class: xl68, width: 64"] 19:00
[/TD]
[TD="class: xl72, width: 64"]1[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"] 6
[/TD]
[TD="class: xl67, width: 64"] John
[/TD]
[TD="class: xl67, width: 64"] Monday
[/TD]
[TD="class: xl68, width: 64"] 19:00
[/TD]
[TD="class: xl72, width: 64"]1[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"] 7
[/TD]
[TD="class: xl67, width: 64"] John
[/TD]
[TD="class: xl67, width: 64"] Tuesday
[/TD]
[TD="class: xl68, width: 64"] 18:00
[/TD]
[TD="class: xl72, width: 64"]1[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"] 8
[/TD]
[TD="class: xl67, width: 64"] John
[/TD]
[TD="class: xl67, width: 64"] Tuesday
[/TD]
[TD="class: xl68, width: 64"] 18:00
[/TD]
[TD="class: xl72, width: 64"]1[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"] 9
[/TD]
[TD="class: xl67, width: 64"] John
[/TD]
[TD="class: xl67, width: 64"] Tuesday
[/TD]
[TD="class: xl68, width: 64"] 19:00
[/TD]
[TD="class: xl72, width: 64"]1[/TD]
[/TR]
</tbody>[/TABLE]