Summing entire sheet, but only higher number with similar items.

Ben Shelby

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.

Mark Cronk

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

Mark Cronk

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

Ben Shelby

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!

Mark Cronk

Hi Ben,

Wish there was a better way. Glad you found something that works. Thank you for contributing to the community.

Mark