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

Ben Shelby
edited 06/14/21 in Formulas and Functions

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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 06/15/21 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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 06/15/21 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.

  • 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
    Mark Cronk ✭✭✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!