Ranking stats with duplicate names

Hi all,

See the attached $$

I am trying to rank companies by top "social media accounts" the problem is companies can have the same name, its just the way the data is. Is there a way to add figures and rank together?

Therefore, instead of:

Company A // 10
Company A // 7
Company B // 2
Company C // 4

I would have

Company A // 17
Company B // 2
Company C // 4

Hi

RANKING sheet
C4

Formula:

=IF(ROWS($C$4:C4)<='USER REPORT'!$A$5,LARGE(SUMIFS('USER REPORT'!$D$10:$D$100,'USER REPORT'!$A$10:$A$100,IF(FREQUENCY(IF('USER REPORT'!$A$10:$A$100<>"",IF('USER REPORT'!$B$10:$B$100=$A$2,MATCH('USER REPORT'!$A$10:$A$100,'USER REPORT'!$A$10:$A$100,0))),ROW('USER REPORT'!$A$10:$A$100)-ROW('USER REPORT'!$A$10)+1),'USER REPORT'!$A$10:$A$100,9.99E+307),'USER REPORT'!$B$10:$B$100,$A$2),ROWS($C$4:C4)),"")

This is array formula!! Press same time Ctrl+Shift+Enter, NOT ENTER!! Then copy down.

A4

Formula:

=IF($C4="","",INDEX('USER REPORT'!$A$10:$A$80,SMALL(IF(IF(MATCH('USER REPORT'!$A$10:$A$80,'USER REPORT'!$A$10:$A$80,0)=ROW('USER REPORT'!$A$10:$A$80)-ROW('USER REPORT'!$A$10)+1,SUMIFS('USER REPORT'!$D$10:$D$80,'USER REPORT'!$B$10:$B$80,$A$2,'USER REPORT'!$A$10:$A$80,'USER REPORT'!$A$10:$A$80))=$C4,ROW('USER REPORT'!$A$10:$A$80)-ROW('USER REPORT'!$A$10)+1),COUNTIF($C$4:$C4,$C4))))

This is array formula!! Press same time Ctrl+Shift+Enter, NOT ENTER!! Then copy down.

B4

Formula:

=IF($C4="","",SUMIF('USER REPORT'!$A$10:$A$80,$A4,'USER REPORT'!$C$10:$C$80))

copy down
Do the same D,E,F columns change a letter!!

USE REPORT sheet
A5

Formula:

=SUM(IF(FREQUENCY(IF($A$10:$A$100<>"",IF($B$10:$B$100=RANKING!$A$2,MATCH($A$10:$A$100,$A$10:$A$100,0))),ROW($A$10:$A$100)-ROW($A$10)+1),1))

This is array formula!! Press same time Ctrl+Shift+Enter, NOT ENTER!! Then copy down.

See the file!! Highlight yellow cell!!

Regard
micope21

Check the file again!! 6# Small error!!

Regard
micope21

Here the correct file!!

I got to get back to work now!!

Will explain later or tomorrow!!

Regard
micope21

Originally Posted by guy13

Thanks mate! I dont think it is 100% just looking at the connections for company named 25 should = 6 but it =0.

In fact if you total the connections on both slides the RANKING slide is about 7k lower

Hi

Can't see anything wrong mate!!

Company 25 is spot on saying = 6!!

Check the file!!

Regard
micope21