Count of changing values

[jingamells]

Hi,

I have a list of data in column A appearing multiple times. In column B is a series of dates that are also duplicates. What I would like in column C is a count per value in Column A every time column B changes. Awful explanation probably!

Hopefully below will help (and what I would like to see in column C)

ID	Date	Desired Outcome
U01000126	24/11/2014	1
U01000126	24/11/2014	1
U01000126	15/05/2015	2
U01000126	15/05/2015	2
U01000126	15/05/2015	2
U01000133	22/06/2015	1
U01000133	22/06/2015	1
U01000133	22/06/2015	1
U01000133	04/10/2015	2
U01000133	04/10/2015	2
U01000134	09/10/2015	1
U01000134	02/11/2015	2
U01000134	02/11/2015	2
U01000134	02/11/2015	2
U01000142	25/08/2015	1
U01000142	08/12/2015	2
U01000142	08/12/2015	2
U01000142	05/05/2016	3
U01000142	05/05/2016	3
U01000142	05/05/2016	3
U01000142	05/05/2016	3
U01000142	05/05/2016	3

<tbody>
</tbody>

 

[shg]

	A​	B​	C​	D​
1​	ID​	Date​	Desired Outcome​	​
2​	U01000126	24 Nov 2014​	1​	C2: {=SUM(IF(A$1:A2 = A2, 1 / COUNTIFS(A$1:A2, A2, B$1:B2, B$1:B2), 0))}
3​	U01000126	24 Nov 2014​	1​
4​	U01000126	15 May 2015​	2​
5​	U01000126	15 May 2015​	2​
6​	U01000126	15 May 2015​	2​
7​	U01000133	22 Jun 2015​	1​
8​	U01000133	22 Jun 2015​	1​
9​	U01000133	22 Jun 2015​	1​
10​	U01000133	4 Oct 2015​	2​
11​	U01000133	4 Oct 2015​	2​
12​	U01000134	9 Oct 2015​	1​
13​	U01000134	2 Nov 2015​	2​
14​	U01000134	2 Nov 2015​	2​
15​	U01000134	2 Nov 2015​	2​
16​	U01000142	25 Aug 2015​	1​
17​	U01000142	8 Dec 2015​	2​
18​	U01000142	8 Dec 2015​	2​
19​	U01000142	5 May 2016​	3​
20​	U01000142	5 May 2016​	3​
21​	U01000142	5 May 2016​	3​
22​	U01000142	5 May 2016​	3​
23​	U01000142	5 May 2016​	3​

 

[jingamells]

Hi,
many thanks for your post, however all the values are coming out as 1?

Thanks

 

[shg]

Paste the formula in the formula bar. Press and hold the Ctrl and Shift keys, then press Enter. If you've done it correctly, the curly braces will appear.

 

[jingamells]

Many thanks SHG