Top 2 total values in a range

with an helpcolum to total the values per row.

then i made a pivot table of the data.

after that i sort the pivot table from big to small.

see the attached file.

notice I get 1) customer 4 and 2) customer 2 as result.

elqueso, welcome to the forum.

The top two values in the range (Jan - Dec) in your example are 5465361, and 6546. Do you mean you want the top two values by sum of whatever those numbers are, on a per customer basis? Or a per product basis? Am I correct in assuming it's over the whole year, or do you want it by month?

In B3 how about..

=SUMPRODUCT(LARGE(($A$11:$A$58=A3)*($C$11:$N$58),{1,2}))

copy down

Please Login or Register  to view this content.

then you have to expand the range in the sumproductformula.

Use a column for yearly total Jan:Dec.

Eg: in O11 use =SUM(C11:N11) to get row total & copy down. Then,

in A3 with CTRL+SHIFT+ENTER

=INDEX(A$11:A$58,MATCH(B3,SUMIF(A$11:A$58,A$11:A$158,O$11:O$58),0))

B3, with CTRL+SHIFT+ENTER

=LARGE(IF(ISNA(MATCH(A$11:A$58,A$2:A2,0)),SUMIF(A$11:A$58,A$11:A$58,O$11:O$58),""),1)

Then select A3:B3 & copy down.

i'm with oeldere but that sheet appears to be blank i did it like this

I like the Pivot Table solution of martindwilson because you don't even need to do any calculations on the data sheet. To get the totals for each customer just add a calculated field to the report then click in the Customer field of the Pivot Table, Filter, Top 10, choose any value from 1 to 10 and filter on the calculated field. It is easy and you can change your criteria with a couple of key strokes.

Formula:

Please Login or Register  to view this content.

This Pivot table makes use of a Calculated field with the above formula as the calculation.

@martindwilson

Thanks for pointing me on the fact the added example is empty.

Here you find the excel file wiht the pivot table.