Calc - Formula help needed counting unique instances/lookup

[Rotaryracer]

Hi all - I need some help writing a formula (or modifying a pivot table) to return specific info in Calc. I can usually muddle through after punching at keys for awhile, but this one has me stumped.

Above is a sample of the table I’m using. I’ve created a separate sheet with all 50 states, with two columns to the right for transaction count and revenue. What I need to do is create a formula that will do a lookup on Column I and see if it matches the state in my summary sheet, then returns the count of unique transactions from Column C and (ideally) the total sales amount in Column H. What is complicating this is the data exported lists each product within a transaction as a separate line item, so for transaction #1715, the customer bought three different products, but it’s listed on three different lines. That’s just one transaction (not three), so need to only count it once.

This spreadsheet is being used to determine economic nexus for sales tax by state where there are both revenue threshold and transaction count factors. I can get the revenue through a pivot table, but am having no luck on the transaction count.

Any help would be greatly appreciated!

Thanks…

Jason

[SM_Riga]

To count unique, you can try something like =SUMPRODUCT("CA"=$D$2:$D$7;$C$2:$C$7<>$C$3:$C$8) This will only work if your export list has Num column sorted, so same transaction ID’s are placed one after another.

[gregors15]

Hi,
Or have a look at the attached sheet, I added a column (J) to your sheet, which should always be 1, then created a pivot table. In column (H) divide the column (F) by it’s self. You can then do any of the calculations you need.
Regards.CountingUnique_20190406.ods