Find highest sum in a group without pivot tables

Hello!

I have a file that contains the product SKU, Vendor Name and then page view count for each SKU (along with a ton of other metadata). For example:

SKU	Vendor Name	Page Views
B001	Vendor A	0
B002	Vendor A	12
B003	Vendor A	3
B004	Vendor A	213
B005	Vendor A	24
B006	Vendor B	56
B007	Vendor B	245
B008	Vendor B	2456
B009	Vendor B	765
B010	Vendor C	23
B011	Vendor C	78
B012	Vendor C	34
B013	Vendor D	14
B014	Vendor D	47
B015	Vendor D	24
B016	Vendor D	462

I'd like to create a list that tells me who the top 20 vendors with the highest total sum of page views are. In the example above the result would be:

Vendor Name Sum of Page Views
Vendor B 3522
Vendor D 547
Vendor A 252
Vendor C 135

Normally I would just use a Pivot table for this and it's done, but the file is gigantic and regularly updated through an ETL data connection which can change the list of vendors and products every week - and Pivot tables just seem to crash Excel when I refresh the data. Is there a way to create the list using a blend of formulas instead?

Any help will be hugely appreciated, even if it's to confirm that Pivot tables are the only way! Thank you

I think pivot table will be the best solution. You can change your data in a "table" by pushing CTR+t (when standing on the data). Then you can stop worrying about changing the list of vendors, only refresh pivot. Piovt tables is dedicated to large emount of data, so maybe somthing else crush Excel while refreshing the data?

Attach sample of your data or whole file if u can.

Top 20 on sheet Top 20


Kind regards
Leo

Originally Posted by ThirdFret

I think I might have a solution here...

If you put your data in cells A2:C19, then the following formula in cell G2 should be able to drag down column G. This formula is an array formula so must be entered by pressing Ctrl+SHIFT+Enter instead of just Enter.

Please Login or Register  to view this content.

Then the following formula in cell F2 can be copied down column F. This one is also an array formula and must be entered by pressing Ctrl+SHIFT+Enter.

Please Login or Register  to view this content.

I'll upload an example here in a minute. Let me know if this works for you.

edit: Attached is an example file. This might produce the desired outcome without relying on a pivot table, but like some other posters have mentioned here, this array solution will likely run A LOT slower than using a pivot table. If you can deal with refreshing every once in a while, I'd suggest taking that route. Hope this helps, though!

Thank you ThirdFret! This worked perfectly :D

Great! Glad I could help.

Remember to mark the thread as SOLVED by going to the "Thread Tools" drop-down at the top of the thread!