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:

A	B	C	D
11/15/18	19816	Purchase	Candle
11/15/18	19816	Purchase	Candle
11/15/18	19817	Comp	Gloves
11/15/18	19818	20% Off Sale	Hat
11/16/18	19819	Purchase	Bath Bomb

<tbody>
</tbody>

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.