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]

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A4&B2:B4&C2:C4<>"",MATCH(A2:A4&B2:B4&C2:C4,A2:A4&B2:B4&C2:C4,0)),ROW($A$2:$C$4)-ROW(INDEX($A$2:$C$4,1,1))+1),1))<strike></strike>

 

[Marcelo Branco]

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"]
<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]

No need of any formula in column D since COUNTIFS always return a result greater than 0 (???)

M.

 

[Joyner]

Put a one at each unique item blank for dups?

=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2,C$2:$C2,C2)=1,1,"")

copied down

Could filter on ones then

 

[Joyner]

Put a one at each unique item blank for dups?

=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2,C$2:$C2,C2)=1,1,"")

copied down

Could filter on ones then

Of course I can get the same results with a simple Pivot Table and a unique count if needed