Count Unique If

[CROY1985]

Hi all

Long time since ive posted on here!

I have a little problem and hoping some can help:

I have a list of branches (will be multiple rows per branch) and the region they sit within (North, Central or South) - branches will always be grouped together but the region might not be. And i want to number the unique branches within its region: For the purpose of this i'd like to avoid sorting the data and instead do it by formula:

So for example below i'd want to formulate the output in the 3rd column based upon the first 2 columns:

Branch	Region	Numbered
1​	North	North 1
2​	Central	Central 1
2​	Central	Central 1
3​	South	South 1
4​	Central	Central 2
5​	North	North 2
6​	North	North 3
6​	North	North 3
7​	Central	Cental 3
8​	South	South 2
9​	South	South 3
10​	North	North 4
10​	North	North 4

Any help would be hugely appreciated!

Thanks

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

+Fluff 1.xlsm
	I	J	K	L
1	Branch	Region	Numbered
2	1	North	North 1	North 1
3	2	Central	Central 1	Central 1
4	2	Central	Central 1	Central 1
5	3	South	South 1	South 1
6	4	Central	Central 2	Central 2
7	5	North	North 2	North 2
8	6	North	North 3	North 3
9	6	North	North 3	North 3
10	7	Central	Cental 3	Central 3
11	8	South	South 2	South 2
12	9	South	South 3	South 3
13	10	North	North 4	North 4
14	10	North	North 4	North 4
Data

Cell Formulas
Range	Formula
L2:L14	L2	=J2&" "&SUMPRODUCT(IF(J$2:J2=J2,1/COUNTIFS(I$2:I2,I$2:I2,J$2:J2,J2)))