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)

Agent	Day	Time	Formula
John	Monday	18:00	3
John	Monday	18:00
John	Monday	18: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)???

Agent	Day	Time	Formula
John	Monday	18:00	1
John	Monday	18:00
John	Monday	18:00

<tbody>
</tbody>

 

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

	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

<tbody>
</tbody>

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