Excluding duplicates from formula

Tiara Rea-Palmer

Hi there! Had a wonderful talk with a Pro Support the other day, and he helped me write an incredible formula for counting "Left to Sell" value on sales items our team is selling. However, I noticed after the fact that it is counting each item as 1 sale, and there may be some sales that cover multiple items but should only count as 1 sale.

For example, in the screenshot below, there is a "-1" value in the # Left column because # Sold is higher than it should be:

This is because # Sold is referencing the following sheet:

and counting each separate line as a unique sale. These three I've outlined are for the same "Domain Name" running at various dates throughout 1 week.

I hope I'm explaining this right. But essentially, I need to remove duplicate entries from this formula that is in # Sold column:

=COUNTIFS({Paid Placements - Placement Page}, [Placement Page]@row, {Paid Placements - Start Date}, >=Date@row, {Paid Placements - Start Date}, <=[End Date]@row)

Is there a way to do that in the formula?

Mike matthys

If i understand this right, you want items where sertain data in multiple lines are the same show as 1 count

in your data sheet add a column - autonumber system - autonumber , call it created, and click save, after you save it will start filling up.

What were gonna do now is show the first (min) or last (max) entry of the multiple lines and check the checkbox

So add another column and make it checkbox

call it whatever you want

put code in it like this (adjust it to your needs)

=IF(created@row = MIN(COLLECT(created:created, [Domain name]:[Domain name], [Domain name]@row, [Start date]:[Start date], [Start date]@row)), 1, 0)

This code will check if there are multiple entrys with domain namen and the same date and will check the first entry , and ofc it will check the single entries aswell :)

in your count you can now add check if checkbox is 1 :)

=COUNTIFS({Paid Placements - Placement Page}, [Placement Page]@row, {Paid Placements - Start Date}, >=Date@row, {Paid Placements - Start Date}, <=[End Date]@row, {Paid Placements - checkbox};1)

dont copy paste the code, you gonna need to crosslink {Paid Placements - checkbox} :)

Tiara Rea-Palmer

hmm, when I try to use that formula in the checkbox column, it comes back unparseable. I had to amend the names a bit, since we already utilize a column with autonumber, so it wouldn't let me create another one.

=IF(created@row = MIN(COLLECT(Placement ID:Placement ID, [Domain name]:[Domain name], [Domain name]@row, [Start date]:[Start date], [Start date]@row)), 1, 0)