Excluding duplicates from formula
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?
Answers
-
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} :)
-
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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!