Dynamic Range Countif

[SonnyKali]

Hey everyone

I hope someone can help me with the following.

I have a sheet with companies (Column A) and their subsidiaries(Column B) and the country where those sub sidiariesoperate (Column C). It looks something like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Company
[/TD]
[TD]Subsidiary
[/TD]
[TD]Location
[/TD]
[/TR]
[TR]
[TD]Company 1
[/TD]
[TD]A
[/TD]
[TD]US
[/TD]
[/TR]
[TR]
[TD]Company 1
[/TD]
[TD]B
[/TD]
[TD]JP
[/TD]
[/TR]
[TR]
[TD]Company 1
[/TD]
[TD]C
[/TD]
[TD]CH
[/TD]
[/TR]
[TR]
[TD]Company 2
[/TD]
[TD]D
[/TD]
[TD]FR
[/TD]
[/TR]
[TR]
[TD]Company 3
[/TD]
[TD]E
[/TD]
[TD]JP
[/TD]
[/TR]
[TR]
[TD]Company 3
[/TD]
[TD]F
[/TD]
[TD]JP
[/TD]
[/TR]
[TR]
[TD]Company 4
[/TD]
[TD]G
[/TD]
[TD]FR
[/TD]
[/TR]
[TR]
[TD]Company 4
[/TD]
[TD]H
[/TD]
[TD]US
[/TD]
[/TR]
[TR]
[TD]Company 4
[/TD]
[TD]I
[/TD]
[TD]UK
[/TD]
[/TR]
[TR]
[TD]Company 4
[/TD]
[TD]J
[/TD]
[TD]UK
[/TD]
[/TR]
[TR]
[TD]Company 4
[/TD]
[TD]K
[/TD]
[TD]UK
[/TD]
[/TR]
[TR]
[TD]Company 5
[/TD]
[TD]L
[/TD]
[TD]UK
[/TD]
[/TR]
[TR]
[TD]Company 5
[/TD]
[TD]M
[/TD]
[TD]US
[/TD]
[/TR]
[TR]
[TD]Company 5
[/TD]
[TD]N
[/TD]
[TD]US
[/TD]
[/TR]
[TR]
[TD]Company 5
[/TD]
[TD]O
[/TD]
[TD]US
[/TD]
[/TR]
</tbody>[/TABLE]


I use the COUNTIF function to determine the number of subsidaries per company withiin a given country. I do this by manually adjusting the range within the COUNTIF function. Is there a more easy way do this, by automatically determining the range per company. So as in the example, in the case of company 1 range A2:A4, company 2 A5, company 3 A6:A7, and so on.

Thanks in advance!

 

[MARZIOTULLIO]

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

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

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

1​

[/TD]
[TD]Company 1[/TD]
[TD]A[/TD]
[TD]US[/TD]
[TD][/TD]
[TD][/TD]
[TD]JP[/TD]
[TD]CH[/TD]
[TD]FR[/TD]
[TD]UK[/TD]
[TD]US[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Company 1[/TD]
[TD]B[/TD]
[TD]JP[/TD]
[TD][/TD]
[TD]Company 1[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

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

3​

[/TD]
[TD]Company 1[/TD]
[TD]C[/TD]
[TD]CH[/TD]
[TD][/TD]
[TD]Company 2[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

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

4​

[/TD]
[TD]Company 2[/TD]
[TD]D[/TD]
[TD]FR[/TD]
[TD][/TD]
[TD]Company 3[/TD]
[TD]

2​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

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

5​

[/TD]
[TD]Company 3[/TD]
[TD]E[/TD]
[TD]JP[/TD]
[TD][/TD]
[TD]Company 4[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

3​

[/TD]
[TD]

1​

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

6​

[/TD]
[TD]Company 3[/TD]
[TD]F[/TD]
[TD]JP[/TD]
[TD][/TD]
[TD]Company 5[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

3​

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

7​

[/TD]
[TD]Company 4[/TD]
[TD]G[/TD]
[TD]FR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Company 4[/TD]
[TD]H[/TD]
[TD]US[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]Company 4[/TD]
[TD]I[/TD]
[TD]UK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]Company 4[/TD]
[TD]J[/TD]
[TD]UK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]Company 4[/TD]
[TD]K[/TD]
[TD]UK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]Company 5[/TD]
[TD]L[/TD]
[TD]UK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]Company 5[/TD]
[TD]M[/TD]
[TD]US[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]Company 5[/TD]
[TD]N[/TD]
[TD]US[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]Company 5[/TD]
[TD]O[/TD]
[TD]US[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

g2=COUNTIFS($A$1:$A$15,$E2,$C$1:$C$15,G$1) copy across and down

 

[SonnyKali]

Works Perfectly. Major thanks!

 

[MARZIOTULLIO]

you are welcome