Help with counting the number of events in a spreadsheet based on multiple criteria.

[sasha123]

Hi all,

I'm not sure my title explains this question well but I am struggling to label my data in a specific way.
I have a spreadsheet with where individuals (identified by a UR) have more than one scan in their pregnancy. Each pregnancy is identified by its due date.
I am wanting to find a formula to count the number of times a person has a pregnancy in my dataset regardless of the number of scans. This can occur 5+ times for some people.
In the example below, the UR 25016151 has two pregnancies - the pregnancy ID for the pregnancy with the due date 21/8/19 should be labelled 1 in each row with the correlating scan as it is the first pregnancy in this set, and then the second due date of 2/12/20 should be labelled 2. All other URs only have 1 pregnancy in this set and hence their pregnancy ID should be 1.

Thanks very much in advance, please let me know if any further clarification is needed!

Sample Data.xlsx
	A	B	C	D	E
1	Exam date	DOB	UR	Due Date	Pregnancy ID
2	7/25/19	2/18/95	25016151	8/21/19
3	6/27/19	2/18/95	25016151	8/21/19
4	4/12/19	2/18/95	25016151	8/21/19
5	11/4/20	12/2/78	2861802	12/1/20
6	10/6/20	12/2/78	2861802	12/1/20
7	9/8/20	12/2/78	2861802	12/1/20
8	10/28/20	2/18/95	25016151	12/2/20
9	9/10/20	2/18/95	25016151	12/2/20
10	7/22/20	2/18/95	25016151	12/2/20
11	11/5/20	11/8/88	26281844	12/2/20
12	10/21/20	11/8/88	26281844	12/2/20
13	10/8/20	11/8/88	26281844	12/2/20
14	9/10/20	11/8/88	26281844	12/2/20
15	11/6/20	10/19/94	26282656	12/2/20
16	10/23/20	10/19/94	26282656	12/2/20
17	11/2/20	4/17/88	26286627	12/2/20
18	10/6/20	4/17/88	26286627	12/2/20
19	9/16/20	4/17/88	26286627	12/2/20
20	7/15/20	4/17/88	26286627	12/2/20
Sheet1

 

[cmowla]

Hi.

Is this correct? (Edit: No. Something's off. Will post again soon.)

With 2 helper columns (which you can hide, after you carry the formulas in them down), I have a report/summary of the number of pregnancies each individual had. (Most of these are dynamic formulas, as I see that you have Office 365. But they would work in Office 2021 also.) You do not carry down the formula that's in column i. (It automatically expands on its own.) But carry down the formulas in the other 3 columns.

Blank.xlsb
	A	B	C	D	E	F	G	H	I	J
1	Exam date	DOB	UR	Due Date	Pregnancy ID	Helper Column (Hide me)	Number of Pregnancies (Hide Me)		Report/Summary
2	7/25/2019	2/18/1995	25016151	8/21/2019		0	2		25016151	had 2 pregnancies.
3	6/27/2019	2/18/1995	25016151	8/21/2019		0	2		2861802	had 2 pregnancies.
4	4/12/2019	2/18/1995	25016151	8/21/2019		1	2		26281844	had 2 pregnancies.
5	11/4/2020	12/2/1978	2861802	12/1/2020		0	1		26282656	had 1 pregnancy.
6	10/6/2020	12/2/1978	2861802	12/1/2020		0	1		26286627	had 1 pregnancy.
7	9/8/2020	12/2/1978	2861802	12/1/2020		1	1
8	10/28/2020	2/18/1995	25016151	12/2/2020		0	2
9	9/10/2020	2/18/1995	25016151	12/2/2020		0	2
10	7/22/2020	2/18/1995	25016151	12/2/2020		1	2
11	11/5/2020	11/8/1988	26281844	12/2/2020		0	1
12	10/21/2020	11/8/1988	26281844	12/2/2020		0	1
13	10/8/2020	11/8/1988	26281844	12/2/2020		0	1
14	9/10/2020	11/8/1988	26281844	12/2/2020		1	1
15	11/6/2020	10/19/1994	26282656	12/2/2020		0	1
16	10/23/2020	10/19/1994	26282656	12/2/2020		1	1
17	11/2/2020	4/17/1988	26286627	12/2/2020		0	0
18	10/6/2020	4/17/1988	26286627	12/2/2020		0	0
19	9/16/2020	4/17/1988	26286627	12/2/2020		0	0
20	7/15/2020	4/17/1988	26286627	12/2/2020		0	0
Sheet6 (2)

Cell Formulas
Range	Formula
F2:F20	F2	=IF(AND(C2<>C3,C3<>""),1,0)
G2:G20	G2	=SUM(IF(C$2:C20=C2,F$2:F20,0))
I2:I6	I2	=UNIQUE(C$2:C20)
J2:J20	J2	=IF(I2<>"","had "&G2&IF(G2>1," pregnancies."," pregnancy."),"")
Dynamic array formulas.

 

[cmowla]

Okay, yet another helper column. So is this correct? (So now, carry down the formulas in all columns except for column K.)

Blank.xlsb
	A	B	C	D	E	F	G	H	I	J	K
1	Exam date	DOB	UR	Due Date	Pregnancy ID	Helper Column (Hide me)	Number of Pregnancies (Hide me)		Helper Column (Hide me)	Report/Summary
2	7/25/2019	2/18/1995	25016151	8/21/2019		0	2		2	25016151 pregnancies. for individual:	25016151
3	6/27/2019	2/18/1995	25016151	8/21/2019		0	2		1	2861802 pregnancies. for individual:	2861802
4	4/12/2019	2/18/1995	25016151	8/21/2019		1	2		1	26281844 pregnancies. for individual:	26281844
5	11/4/2020	12/2/1978	2861802	12/1/2020		0	1		1	26282656 pregnancies. for individual:	26282656
6	10/6/2020	12/2/1978	2861802	12/1/2020		0	1		1	26286627 pregnancies. for individual:	26286627
7	9/8/2020	12/2/1978	2861802	12/1/2020		1	1
8	10/28/2020	2/18/1995	25016151	12/2/2020		0	2
9	9/10/2020	2/18/1995	25016151	12/2/2020		0	2
10	7/22/2020	2/18/1995	25016151	12/2/2020		1	2
11	11/5/2020	11/8/1988	26281844	12/2/2020		0	1
12	10/21/2020	11/8/1988	26281844	12/2/2020		0	1
13	10/8/2020	11/8/1988	26281844	12/2/2020		0	1
14	9/10/2020	11/8/1988	26281844	12/2/2020		1	1
15	11/6/2020	10/19/1994	26282656	12/2/2020		0	1
16	10/23/2020	10/19/1994	26282656	12/2/2020		1	1
17	11/2/2020	4/17/1988	26286627	12/2/2020		0	1
18	10/6/2020	4/17/1988	26286627	12/2/2020		0	1
19	9/16/2020	4/17/1988	26286627	12/2/2020		0	1
20	7/15/2020	4/17/1988	26286627	12/2/2020		1	1
Sheet6 (3)

Cell Formulas
Range	Formula
F2:F20	F2	=IF(AND(C2<>C3),1,0)
G2:G20	G2	=SUM(IF(C$2:C20=C2,F$2:F20,0))
I2:I20	I2	=IFERROR(VLOOKUP(K2,C$2:G20,5),"")
J2:J20	J2	=IF(K2<>"",K2&IF(K2>1," pregnancies."," pregnancy.")&" for individual: ","")
K2:K6	K2	=UNIQUE(C$2:C20)
Dynamic array formulas.

 

[cmowla]

Note: I just rearranged it so that you can carry down the formulas easier. (Carry down the formulas for all columns except for column K.)

Edit: Made an obvious mistake. For the third time!

Blank.xlsb
	A	B	C	D	E	F	G	H	I	J	K
1	Exam date	DOB	UR	Due Date	Pregnancy ID	Helper Column (Hide me)	Number of Pregnancies (Hide me)		Helper Column (Hide me)	Report/Summary
2	7/25/2019	2/18/1995	25016151	8/21/2019		0	2		2	2 pregnancies for individual:	25016151
3	6/27/2019	2/18/1995	25016151	8/21/2019		0	2		1	1 pregnancy for individual:	2861802
4	4/12/2019	2/18/1995	25016151	8/21/2019		1	2		1	1 pregnancy for individual:	26281844
5	11/4/2020	12/2/1978	2861802	12/1/2020		0	1		1	1 pregnancy for individual:	26282656
6	10/6/2020	12/2/1978	2861802	12/1/2020		0	1		1	1 pregnancy for individual:	26286627
7	9/8/2020	12/2/1978	2861802	12/1/2020		1	1
8	10/28/2020	2/18/1995	25016151	12/2/2020		0	2
9	9/10/2020	2/18/1995	25016151	12/2/2020		0	2
10	7/22/2020	2/18/1995	25016151	12/2/2020		1	2
11	11/5/2020	11/8/1988	26281844	12/2/2020		0	1
12	10/21/2020	11/8/1988	26281844	12/2/2020		0	1
13	10/8/2020	11/8/1988	26281844	12/2/2020		0	1
14	9/10/2020	11/8/1988	26281844	12/2/2020		1	1
15	11/6/2020	10/19/1994	26282656	12/2/2020		0	1
16	10/23/2020	10/19/1994	26282656	12/2/2020		1	1
17	11/2/2020	4/17/1988	26286627	12/2/2020		0	1
18	10/6/2020	4/17/1988	26286627	12/2/2020		0	1
19	9/16/2020	4/17/1988	26286627	12/2/2020		0	1
20	7/15/2020	4/17/1988	26286627	12/2/2020		1	1
Sheet6 (3)

Cell Formulas
Range	Formula
F2:F20	F2	=IF(AND(C2<>C3),1,0)
G2:G20	G2	=SUM(IF(C$2:C20=C2,F$2:F20,0))
I2:I20	I2	=IFERROR(VLOOKUP(K2,C$2:G20,5),"")
J2:J20	J2	=IF(I2<>"",I2&IF(I2>1," pregnancies"," pregnancy")&" for individual: ","")
K2:K6	K2	=UNIQUE(C$2:C20)
Dynamic array formulas.

 

[sasha123]

Hi,

Thanks so much for your response.
Unfortunately that's not exactly what I want - it needs to be a running count of the number of times the UR occurs in the dataset - so the pregnancy ID in E2-4 should be 1 and then E8-10 should be 2.
I actually found a solution that works for me:

=MATCH($D2, UNIQUE(FILTER($D$2:$D$20, $C$2:$C$20 = $C2)), 0)

Will leave that here if anyone else has a similar problem in the future.