Finding like items and adding together values

MTaber

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!

Nick Korna

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.

Nick Korna

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

Thank you, Nick!