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
Check out the Formula Handbook template!