Summing entire sheet, but only higher number with similar items.
Okay so I'm trying to create a rough budget for a project, but have received multiple bids for some categories. I want to sum all the items cost, but with multiple items, only take the higher cost.
Here's roughly what I mean:
Item Price
Appliances 12000
Appliances 10000
Glass 8000
Stone 7000
So I want to sum the price column, but only take the larger number from appliances(or anything with the same name). I know I could make a large Max(collect()) chain within a sum function and write out every item I have duplicates of, but Id like to avoid that. I have a rather lengthy list with several duplicates.
I was wondering if there's a way to create a function to check for duplicates, then maybe use that with max and sum everything.
Thank you to anyone who helps in advance.
Best Answer
-
Hi Ben,
Someone may have an easier way but here's my idea. Add a checkbox column [Duplicate] with the formula:
=IF(COUNTIF(Item1:Item@row, item@row)>1, 1, 0)
That will flag/check all duplicate items but leave the first instance unchecked.
Then insert a text/number column [Max] with the column formula:
=MAX(Collect(price:price, item:item, item@row)
This will enter the max price of that item in every row with that item.
To sum your max prices use the formula:
=SUMIFS(max:max, duplicate:duplicate, @cell =0)
It will sum the non duplicate items using the max price.
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Hi Ben,
Someone may have an easier way but here's my idea. Add a checkbox column [Duplicate] with the formula:
=IF(COUNTIF(Item1:Item@row, item@row)>1, 1, 0)
That will flag/check all duplicate items but leave the first instance unchecked.
Then insert a text/number column [Max] with the column formula:
=MAX(Collect(price:price, item:item, item@row)
This will enter the max price of that item in every row with that item.
To sum your max prices use the formula:
=SUMIFS(max:max, duplicate:duplicate, @cell =0)
It will sum the non duplicate items using the max price.
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
I was trying to avoid adding excess columns, but this is the only way I can really think of either.
Thank you for the help!
-
Hi Ben,
Wish there was a better way. Glad you found something that works. Thank you for contributing to the community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!