Accumulate Amounts based on condition

[bobkap]

I have a report I need to run weekly. Typically, there are around 2,000 rows of data. Where a specific cell in the same column have the same data, I want to sum the data in another cell on the same row (vlookup), plus count the number of entries. As an example:

57 Apples
12 Bananas
6 Pears
7 Pears
6 Bananas

How do I use vba to collect and store my total sum AND count of each fruit? OR, should I try to accomplish this using a pivot table?

 

[alansidman]

Can be accomplished with one action employing Power Query. Here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column2"}, {{"Sum", each List.Sum([Column1]), type number}, {"Count", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"

Book1
	A	B	C
1	Column2	Sum	Count
2	Apples	57	1
3	Bananas	18	2
4	Pears	13	2
Sheet2