RANK segmented sales w/ SUMPRODUCT?

[rails85]

Hi all! I'm basically at a breaking point. I am trying to rank a file with sales by customer but the sales are broken out by a sub segment. I need to rank them at the total customer level. Below is a makeshift example of how my data looks (the rank column is the result I want):

[TABLE="width: 265"]
<tbody>[TR]
[TD="align: center"]Name[/TD]
[TD="align: center"]Sales[/TD]
[TD="align: center"]Total Sales[/TD]
[TD="align: center"]Rank[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]227[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]C[/TD]
[TD="align: center"]84[/TD]
[TD="align: center"]803[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]D[/TD]
[TD="align: center"]635[/TD]
[TD="align: center"]648[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]65[/TD]
[TD="align: center"]227[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]C[/TD]
[TD="align: center"]645[/TD]
[TD="align: center"]803[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]89[/TD]
[TD="align: center"]227[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]C[/TD]
[TD="align: center"]74[/TD]
[TD="align: center"]803[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]D[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]648[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]68[/TD]
[TD="align: center"]227[/TD]
[TD="align: center"]3[/TD]
[/TR]
</tbody>[/TABLE]

What I'm looking for is to combine the total sales which I got with sumproduct (or sumif) and the rank into one formula.

Thanks much in advance!

 

[Matty]

Welcome to MrExcel...

Why not create a summary table of the sales and then do the rank within this table?

Matty

 

[bob33]

Hi all! I'm basically at a breaking point. I am trying to rank a file with sales by customer but the sales are broken out by a sub segment. I need to rank them at the total customer level. Below is a makeshift example of how my data looks (the rank column is the result I want):

[TABLE="width: 265"]
<tbody>[TR]
[TD="align: center"]Name[/TD]
[TD="align: center"]Sales[/TD]
[TD="align: center"]Total Sales[/TD]
[TD="align: center"]Rank[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]227[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]C[/TD]
[TD="align: center"]84[/TD]
[TD="align: center"]803[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]D[/TD]
[TD="align: center"]635[/TD]
[TD="align: center"]648[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]65[/TD]
[TD="align: center"]227[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]C[/TD]
[TD="align: center"]645[/TD]
[TD="align: center"]803[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]89[/TD]
[TD="align: center"]227[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]C[/TD]
[TD="align: center"]74[/TD]
[TD="align: center"]803[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]D[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]648[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]68[/TD]
[TD="align: center"]227[/TD]
[TD="align: center"]3[/TD]
[/TR]
</tbody>[/TABLE]


What I'm looking for is to combine the total sales which I got with sumproduct (or sumif) and the rank into one formula.

Thanks much in advance!

Why not try this in Col D (assuming your headers are row 1:
=rank(c2,C$2:C$10)

 

[rails85]

Thanks Bob & Matty!. But what I want to do is skip the process of making a column for total sales before I rank. I actually want the rank in column D to be in column C. I've seen it done in other forums but the difference is those ranks were done at sub class level rather than an aggregate level.

 

[Marcelo Branco]

Maybe something like this

Create a list of unique names in column E - you can achieve this easily using Data > Remove Duplicates

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Name​

[/TD]
[TD]

Sales​

[/TD]
[TD]

Rank​

[/TD]
[TD][/TD]
[TD]

Name Unique​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

A​

[/TD]
[TD]

5​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

A​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

B​

[/TD]
[TD]

85​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

C​

[/TD]
[TD]

84​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

D​

[/TD]
[TD]

635​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

A​

[/TD]
[TD]

65​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

C​

[/TD]
[TD]

645​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

A​

[/TD]
[TD]

89​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

C​

[/TD]
[TD]

74​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

D​

[/TD]
[TD]

13​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

A​

[/TD]
[TD]

68​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in C2 copied down
<sumif($a$2:$a$11,$e$2:$e$11,$b$2:$b$11)))+1
=SUMPRODUCT(--(SUMIF($A$2:$A$11,A2,$B$2:$B$11) < SUMIF($A$2:$A$11,$E$2:$E$11,$B$2:$B$11)))+1

Hope this helps

M.</sumif($a$2:$a$11,$e$2:$e$11,$b$2:$b$11)))+1

 

[rails85]

Maybe something like this

Create a list of unique names in column E - you can achieve this easily using Data > Remove Duplicates

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Name​

[/TD]
[TD]

Sales​

[/TD]
[TD]

Rank​

[/TD]
[TD][/TD]
[TD]

Name Unique​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

A​

[/TD]
[TD]

5​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

A​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

B​

[/TD]
[TD]

85​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

C​

[/TD]
[TD]

84​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

D​

[/TD]
[TD]

635​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

A​

[/TD]
[TD]

65​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

C​

[/TD]
[TD]

645​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

A​

[/TD]
[TD]

89​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

C​

[/TD]
[TD]

74​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

D​

[/TD]
[TD]

13​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

A​

[/TD]
[TD]

68​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in C2 copied down
<sumif($a$2:$a$11,$e$2:$e$11,$b$2:$b$11)))+1
=SUMPRODUCT(--(SUMIF($A$2:$A$11,A2,$B$2:$B$11) < SUMIF($A$2:$A$11,$E$2:$E$11,$B$2:$B$11)))+1

Hope this helps

M.

Ah that's exactly the result I want. Is it to much to ask for to not have to make a unique name column? I'm building this for a larger report and the data set will change all the time (it will pull from a database). The final goal is to create an automated top and bottom 10 rank report based on total sales rank and to have that report be dynamic based on the data that's brought into it.</sumif($a$2:$a$11,$e$2:$e$11,$b$2:$b$11)))+1

 

[Marcelo Branco]

<sumif($a$2:$a$11,$e$2:$e$11,$b$2:$b$11)))+1

Ah that's exactly the result I want. Is it to much to ask for to not have to make a unique name column?

Maybe this....


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]

Name​

[/td][td]

Sales​

[/td][td]

Rank​

[/td][/tr]


[tr][td]

2​

[/td][td]

A​

[/td][td]

5​

[/td][td]

3​

[/td][/tr]


[tr][td]

3​

[/td][td]

B​

[/td][td]

85​

[/td][td]

4​

[/td][/tr]


[tr][td]

4​

[/td][td]

C​

[/td][td]

84​

[/td][td]

1​

[/td][/tr]


[tr][td]

5​

[/td][td]

D​

[/td][td]

635​

[/td][td]

2​

[/td][/tr]


[tr][td]

6​

[/td][td]

A​

[/td][td]

65​

[/td][td]

3​

[/td][/tr]


[tr][td]

7​

[/td][td]

C​

[/td][td]

645​

[/td][td]

1​

[/td][/tr]


[tr][td]

8​

[/td][td]

A​

[/td][td]

89​

[/td][td]

3​

[/td][/tr]


[tr][td]

9​

[/td][td]

C​

[/td][td]

74​

[/td][td]

1​

[/td][/tr]


[tr][td]

10​

[/td][td]

D​

[/td][td]

13​

[/td][td]

2​

[/td][/tr]


[tr][td]

11​

[/td][td]

A​

[/td][td]

68​

[/td][td]

3​

[/td][/tr]
[/table]

Formula in C2 copied down
=SUMPRODUCT(--(SUMIF($A$2:$A$11,A2,$B$2:$B$11) < (COUNTIF(OFFSET($A$2:$A$11,,,ROW($A$2:$A$11)-ROW($A$2)+1),$A$2:$A$11)=1)*SUMIF($A$2:$A$11,$A$2:$A$11,$B$2:$B$11)))+1

M.
</sumif($a$2:$a$11,$e$2:$e$11,$b$2:$b$11)))+1

 

[rails85]

Thanks Marcelo.. it works! Sadly, my file has about 20k lines and it took about 25 minutes to calculate. Since I'm trying to do this with a few columns I don't know if this can be implemented. What if I had a pre-build sumif formula and wanted to rank off of that?