Finding like items and adding together values

Options

I am looking for a way to find the item with 1). The highest volume of purchases (ordered the most number of times) and 2). The highest $ spend (which item had the highest total amount of money spent on it).


Any help would be greatly appreciated!

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @MTaber,

    There are a few ways round this.

    You can add helper columns (COUNTIF & SUMIF):

    =COUNTIF(Item:Item, Item@row)

    =SUMIF(Item:Item, Item@row, [PO Spend]:[PO Spend])

    You can then use the values from these to find the most common/biggest spends:

    Most common:

    =INDEX(Item:Item, MAX(Countif:Countif))

    Biggest total spend:

    =INDEX(COLLECT(Item:Item, Sumif:Sumif, MAX(Sumif:Sumif)), 1)

    Alternatively you can add a Row ID column (Sequential numbers or use autonumber) to get a table with the unique values, count & sum easy to see.

    For unique values:

    =IFERROR(INDEX(DISTINCT(Item:Item), [Row ID]@row, 1), "")

    For count:

    =IF([Unique values]@row <> "", COUNTIF(Item:Item, [Unique values]@row), "")

    =IF([Unique values]@row <> "", SUMIF(Item:Item, [Unique values]@row, [PO Spend]:[PO Spend]), "")

    Most common would then be:

    =INDEX(COLLECT([Unique values]:[Unique values], Count:Count, MAX(Count:Count)), 1)

    Most total spend:

    =INDEX(COLLECT([Unique values]:[Unique values], Sum:Sum, MAX(Sum:Sum)), 1)

    Here is a screenshot demonstrating both at once (roughly using your data, just with A-E instead of the vehicle names):

    As yet another alternative you could use a data row report, group by the Item and then sort by count of row IDs or the total.

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @MTaber,

    There are a few ways round this.

    You can add helper columns (COUNTIF & SUMIF):

    =COUNTIF(Item:Item, Item@row)

    =SUMIF(Item:Item, Item@row, [PO Spend]:[PO Spend])

    You can then use the values from these to find the most common/biggest spends:

    Most common:

    =INDEX(Item:Item, MAX(Countif:Countif))

    Biggest total spend:

    =INDEX(COLLECT(Item:Item, Sumif:Sumif, MAX(Sumif:Sumif)), 1)

    Alternatively you can add a Row ID column (Sequential numbers or use autonumber) to get a table with the unique values, count & sum easy to see.

    For unique values:

    =IFERROR(INDEX(DISTINCT(Item:Item), [Row ID]@row, 1), "")

    For count:

    =IF([Unique values]@row <> "", COUNTIF(Item:Item, [Unique values]@row), "")

    =IF([Unique values]@row <> "", SUMIF(Item:Item, [Unique values]@row, [PO Spend]:[PO Spend]), "")

    Most common would then be:

    =INDEX(COLLECT([Unique values]:[Unique values], Count:Count, MAX(Count:Count)), 1)

    Most total spend:

    =INDEX(COLLECT([Unique values]:[Unique values], Sum:Sum, MAX(Sum:Sum)), 1)

    Here is a screenshot demonstrating both at once (roughly using your data, just with A-E instead of the vehicle names):

    As yet another alternative you could use a data row report, group by the Item and then sort by count of row IDs or the total.

  • MTaber
    MTaber ✭✭
    Options

    Thank you, Nick!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!