Finding like items and adding together values
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

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 AE 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

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 AE 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.

Thank you, Nick!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.9K Get Help
 379 Global Discussions
 209 Industry Talk
 441 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 300 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!