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