Total of companies in sheet But having a problem

[Mikelowrey]

Hello,

I have a sheet with over 50K companies, now here is the thing, some companies repeats themselves in the sheet because the have multiple licenses. What I would like to know is how can I know the total of companies "by name" even tho they have multiples licenses.

Hopefully I wrote the right way..

thank you !

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(A2:A50000=""),MATCH(A2:A1000,A2:A50000,0)),ROW(A2:A50000)-ROW(A2)+1),1))

where A2:A50000 is the range which houses company names.

This gives you a unique count of the companies in your data.

 

[Mikelowrey]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(A2:A50000=""),MATCH(A2:A1000,A2:A50000,0)),ROW(A2:A50000)-ROW(A2)+1),1))

where A2:A50000 is the range which houses company names.

This gives you a unique count of the companies in your data.

ok, so If I have the company name in column G, this is what I should do?

=SUM(IF(FREQUENCY(IF(1-(G2:G50000=""),MATCH(G2:G1000,G2:G50000,0)),ROW(G2:G50000)-ROW(G2)+1),1))

Thanks!

 

[Aladin Akyurek]

ok, so If I have the company name in column G, this is what I should do?

=SUM(IF(FREQUENCY(IF(1-(G2:G50000=""),MATCH(G2:G1000,G2:G50000,0)),ROW(G2:G50000)-ROW(G2)+1),1))

Thanks!

=SUM(IF(FREQUENCY(IF(1-(G2:G50000=""),MATCH(G2:G50000,G2:G50000,0)),ROW(G2:G50000)-ROW(G2)+1),1))

This must be confirmed with control+shift+enter, not just enter.
​

 

[Mikelowrey]

=SUM(IF(FREQUENCY(IF(1-(G2:G50000=""),MATCH(G2:G50000,G2:G50000,0)),ROW(G2:G50000)-ROW(G2)+1),1))

This must be confirmed with control+shift+enter, not just enter.
​

ETA:

I did get a value of 0 at the beginning but then I got a value. So the next questions is the definition of this code, what did it look for?

 

[Aladin Akyurek]

Getting a value of "0"

Control+shift+enter >> Press down the control and the shift keys at the same time, while you hit the enter key. If done correctly, Excel itself puts a pair of { and } around the formula in recognition.

 

[Mikelowrey]

Control+shift+enter >> Press down the control and the shift keys at the same time, while you hit the enter key. If done correctly, Excel itself puts a pair of { and } around the formula in recognition.

Yes, it added the {} but its taking a while to "process". what is it that the code specifically looks for?

 

[Mikelowrey]

This is what I did:

=SUM(IF(FREQUENCY(IF(1-(G3:G80368=""),MATCH(G3:G80368,G3:G80368,0)),ROW(G3:G80368)-ROW(G3)+1),1))

press ctrl shift enter and it still gives me a value of 0

** It did add the {}

eTA:
I did get a final value which took a while. thank you for the great support and quick answers!

 

[Aladin Akyurek]

This is what I did:

=SUM(IF(FREQUENCY(IF(1-(G3:G80368=""),MATCH(G3:G80368,G3:G80368,0)),ROW(G3:G80368)-ROW(G3)+1),1))

press ctrl shift enter and it still gives me a value of 0

** It did add the {}

eTA:
I did get a final value which took a while. thank you for the great support and quick answers!

Would you post a sample 10 rows and indicate the desired outcome for that sample?

 

[Mikelowrey]

Would you post a sample 10 rows and indicate the desired outcome for that sample?

Sure,
total of 80,368 = 69,766

First 10 Row:

10 = 10

ROW FROM G33 TO G48

16 =14

Nice!