Combining COUNTIFS with FREQUENCY?

[chris32094]

Hello!

I have the following set up

Column A - Date of Transaction
Column B - Transaction Number
Column C - Transaction Category ("Purchases", "20% Off Sale", "Employee 10% Off Discount", "Comp")
Column D - Item

For example:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]11/15/18[/TD]
[TD]19816[/TD]
[TD]Purchase[/TD]
[TD]Candle[/TD]
[/TR]
[TR]
[TD]11/15/18[/TD]
[TD]19816[/TD]
[TD]Purchase[/TD]
[TD]Candle[/TD]
[/TR]
[TR]
[TD]11/15/18[/TD]
[TD]19817[/TD]
[TD]Comp[/TD]
[TD]Gloves[/TD]
[/TR]
[TR]
[TD]11/15/18[/TD]
[TD]19818[/TD]
[TD]20% Off Sale[/TD]
[TD]Hat[/TD]
[/TR]
[TR]
[TD]11/16/18[/TD]
[TD]19819[/TD]
[TD]Purchase[/TD]
[TD]Bath Bomb[/TD]
[/TR]
</tbody>[/TABLE]

I'm looking to create a formula that shows me the amount of receipts (B:B) within a given date range (A:A), excluding "Comp" transactions (C:C) WITHOUT DUPLICATES
So if I wanted to see the above for 11/15/18, the formula would return 2 transactions.

I've been trying to combine formulas but I can't seem to work out the addition of the FREQUENCY function.
Any assistance would be GREATLY appreciated.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$6="11/15/18"+0,IF(1-($C$2:$C$6="comp"),$B$2:$B$6)),$B$2:$B$6),1))

 

[chris32094]

control+shift+enter, not just enter:

=sum(if(frequency(if($a$2:$a$6="11/15/18"+0,if(1-($c$2:$c$6="comp"),$b$2:$b$6)),$b$2:$b$6),1))

you are a lifesaver!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[Aladin Akyurek]

you are a lifesaver!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Glad to help.