Formula to check occurances in a column based on multiple criteria

[chrisou]

Hi Guys

I am trying to find a formula in column C of the attached spreadsheet to return a value of 1 or 2 based on whether an occurance has already happened for that day or not.
As explained in column D, if the format (Bol, Gn 1:3 or verrines) is the first series of that day, then a value of 1 should be returned.

However, on the 3 June there are 2 series of the format Bol and Verrines. In this case, I would like the formula to enter in column C a 1 for the first series and a 2 when the second series of those 2 formats were run.

The formula needs to make reference to the day and check the previous formats in column B before allocating a 1 or 2 in column C

Many thanks for your help

 

[cmowla]

Hi, chrisou!

The way I have done this, you will need a "helper column" (a column strictly to do calculations but can be hidden in your sheet) for each format changeover you have. You can have as many of them as you need, but you need to make an additional helper column for each. The formula in Column C is a nested IF statement, and there is a limit to the number of IF statements you can have without concatenating them with a &. (At least in older versions of Excel.) But you can have at least 7 different changeovers without having any problems. (I know that for sure.)

Blank.xlsb
	A	B	C	D	E	F	G
1	Date	Format Changeover	Format frequency
2	1/6/2021	GN 1/3	1	New Day	1	0	0
3	1/6/2021	GN 1/3	1		1	0	0
4	1/6/2021	BOL 130	1		1	1	0
5	1/6/2021	BOL 130	1		1	1	0
6	1/6/2021	VERRINES	1		1	1	1
7	1/6/2021	VERRINES	1		1	1	1
8	1/6/2021	VERRINES	1		1	1	1
9	2/6/2021	BOL 130	1	New Day	0	1	0
10	2/6/2021	BOL 130	1		0	1	0
11	3/6/2021	BOL 130	1	New Day	0	1	0
12	3/6/2021	BOL 130	1		0	1	0
13	3/6/2021	BOL 130	1		0	1	0
14	3/6/2021	VERRINES	1		0	1	1
15	3/6/2021	VERRINES	1		0	1	1
16	3/6/2021	BOL 130	2		0	2	1
17	3/6/2021	BOL 130	2		0	2	1
18	3/6/2021	BOL 130	2		0	2	1
19	3/6/2021	VERRINES	2		0	2	2
20	3/6/2021	VERRINES	2		0	2	2
21	4/6/2021	BOL 130	1	New Day	0	1	0
22	4/6/2021	BOL 130	1		0	1	0
23	4/6/2021	BOL 130	1		0	1	0
Series

Cell Formulas
Range	Formula
C2:C23	C2	=IF(B2="GN 1/3",E2,IF(B2="BOL 130",F2,IF(B2="VERRINES",G2,"?")))
D2:D23	D2	=IF(A1<>A2,"New Day","")
E2:E23	E2	=IF(D2="New Day",IF(B2="GN 1/3",1,0),IF(AND(B1<>B2,B2="GN 1/3"),E1+1,E1))
F2:F23	F2	=IF(D2="New Day",IF(B2="BOL 130",1,0),IF(AND(B1<>B2,B2="BOL 130"),F1+1,F1))
G2:G23	G2	=IF(D2="New Day",IF(B2="VERRINES",1,0),IF(AND(B1<>B2,B2="VERRINES"),G1+1,G1))

I put it to display a "?" in column C should it not recognize the changeover name. (For user-friendliness.)

Let me know if this is what you wanted. Thanks!

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this does what you want directly.

22 09 19.xlsm
	A	B	C
1	Date	Format Changeover	Format frequency
2	1/06/2021	GN 1/3	1
3	1/06/2021	GN 1/3	1
4	1/06/2021	BOL 130	1
5	1/06/2021	BOL 130	1
6	1/06/2021	VERRINES	1
7	1/06/2021	VERRINES	1
8	1/06/2021	VERRINES	1
9	2/06/2021	BOL 130	1
10	2/06/2021	BOL 130	1
11	3/06/2021	BOL 130	1
12	3/06/2021	BOL 130	1
13	3/06/2021	BOL 130	1
14	3/06/2021	VERRINES	1
15	3/06/2021	VERRINES	1
16	3/06/2021	BOL 130	2
17	3/06/2021	BOL 130	2
18	3/06/2021	BOL 130	2
19	3/06/2021	VERRINES	2
20	3/06/2021	VERRINES	2
21	4/06/2021	BOL 130	1
22	4/06/2021	BOL 130	1
23	4/06/2021	BOL 130	1
1 or 2

Cell Formulas
Range	Formula
C2:C23	C2	=IF(AND(A2=A1,B2=B1),C1,1+(COUNTIFS(A$1:A1,A2,B$1:B1,B2)>0))

 

[chrisou]

Hi Cmowla & Peter

Many thanks to both of you for your proposals which both work perfectly. Thank you & well done

Chris

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.