If And Or conditional formatting .

[Sunline]

Hello all ,
Im wanting to get a formular that is able to find matching dates then matching distances col P then matching R# col R .

If all three match result answer is 1 or true or false would be ok .

Ive tried applying contional formatting functions but no joy .

I have highlighted the ones that match with answer 1 as per example sheet .
Thanks .

Excel Workbook
	F	P	R	S
1	Date	Dist	R#	Result
2	13/01/2012	2200	1
3	13/01/2012	2200	2
4	13/01/2012	1150	3
5	13/01/2012	1150	10	1
6	13/01/2012	1150	10	1
7	13/01/2012	1150	10	1
8	13/01/2012	1600	10
9	14/01/2012	2100	5
10	14/01/2012	2100	7	1
11	14/01/2012	2100	7	1
12	18/01/2012	1400	10
13	20/01/2012	1600	2
14	20/01/2012	1600	8	1
15	20/01/2012	1600	8	1
16	20/01/2012	1600	8	1
17	20/01/2012	1600	8	1
18	21/01/2012	1335	7
19	21/01/2012	1200	3
20	27/01/2012	1400	11
21	29/01/2012	2100	1
22	1/02/2012	4800	6
23	3/02/2012	4800	6	1
24	3/02/2012	4800	6	1
25	4/02/2012	1400	5
26	4/02/2012	1400	1
27	8/02/2012	2000	3
Sheet1

 

[T. Valko]

Hello all ,
Im wanting to get a formular that is able to find matching dates then matching distances col P then matching R# col R .

If all three match result answer is 1 or true or false would be ok .

Ive tried applying contional formatting functions but no joy .

I have highlighted the ones that match with answer 1 as per example sheet .
Thanks .
Sheet1

	F	P	R	S
1	Date	Dist	R#	Result
2	13/01/2012	2200	1
3	13/01/2012	2200	2
4	13/01/2012	1150	3
5	13/01/2012	1150	10	1
6	13/01/2012	1150	10	1
7	13/01/2012	1150	10	1
8	13/01/2012	1600	10
9	14/01/2012	2100	5
10	14/01/2012	2100	7	1
11	14/01/2012	2100	7	1
12	18/01/2012	1400	10
13	20/01/2012	1600	2
14	20/01/2012	1600	8	1
15	20/01/2012	1600	8	1
16	20/01/2012	1600	8	1
17	20/01/2012	1600	8	1
18	21/01/2012	1335	7
19	21/01/2012	1200	3
20	27/01/2012	1400	11
21	29/01/2012	2100	1
22	1/02/2012	4800	6
23	3/02/2012	4800	6	1
24	3/02/2012	4800	6	1
25	4/02/2012	1400	5
26	4/02/2012	1400	1
27	8/02/2012	2000	3

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 71px"><COL style="WIDTH: 46px"><COL style="WIDTH: 39px"><COL style="WIDTH: 60px"></COLGROUP><TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

Try this...

Entered in S2 and copied down as needed:

=--(COUNTIFS(F:F,F2,P:P,P2,R:R,R2)>1)

 

[Sunline]

Thanks T.Valco it works perfectly totally matched what i had to do manually .

Is it possible to get it to work when i filter each race ? .
I use col U to filter starting at 1 and of course the function is still picking up data from the whole sheet .
Maybe the only way around this is to make 10 seperate sheets .
Thanks .

 

[T. Valko]

Thanks T.Valco it works perfectly totally matched what i had to do manually .

Is it possible to get it to work when i filter each race ? .
I use col U to filter starting at 1 and of course the function is still picking up data from the whole sheet .
Maybe the only way around this is to make 10 seperate sheets .
Thanks .

Try this...

=--(SUMPRODUCT(SUBTOTAL(3,OFFSET(F$2,ROW(F$2:F$27)-ROW(F$2),0)),--(F$2:F$27=F2),--(P$2:P$27=P2),--(R$2:R$27=R2))>1)

 

[Sunline]

I have just found an error , rows 3 and 4 are the same as per your function but they are two meetings .
Is it possible to include col A (Meet) as well , this will ensure things 100% .

Im just going to try the Sumproduct function now . Sorry to muck you around .

 

[Sunline]

Sorry forgot this sample sheet .

Excel Workbook
	A	B	L	N	O	P
1	Meet	Date	Dist	R#	Result	My error
2	Teaw	23/05/2012	1150	8	0
3	Tren	26/05/2012	1200	2	1	Here
4	Elle	26/05/2012	1200	2	1	Here
5	Elle	26/05/2012	1400	3	0
6	Puke	31/05/2012	1200	7	1
7	Puke	31/05/2012	1200	7	1
8	Elle	2/06/2012	1200	1	0
9	Hast	7/06/2012	1400	8	0
10	Tera	9/06/2012	1200	4	0
11	Elle	16/06/2012	1200	2	0
12	Elle	16/06/2012	1400	9	0
13	Taur	23/06/2012	1200	1	1
14	Taur	23/06/2012	1200	1	1
15	Taur	23/06/2012	1400	2	0
16	Taur	23/06/2012	1400	6	0
Sheet1

 

[T. Valko]

Sorry forgot this sample sheet .
Sheet1

	A	B	L	N	O	P
1	Meet	Date	Dist	R#	Result	My error
2	Teaw	23/05/2012	1150	8	0
3	Tren	26/05/2012	1200	2	1	Here
4	Elle	26/05/2012	1200	2	1	Here
5	Elle	26/05/2012	1400	3	0
6	Puke	31/05/2012	1200	7	1
7	Puke	31/05/2012	1200	7	1
8	Elle	2/06/2012	1200	1	0
9	Hast	7/06/2012	1400	8	0
10	Tera	9/06/2012	1200	4	0
11	Elle	16/06/2012	1200	2	0
12	Elle	16/06/2012	1400	9	0
13	Taur	23/06/2012	1200	1	1
14	Taur	23/06/2012	1200	1	1
15	Taur	23/06/2012	1400	2	0
16	Taur	23/06/2012	1400	6	0

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 52px"><COL style="WIDTH: 71px"><COL style="WIDTH: 46px"><COL style="WIDTH: 43px"><COL style="WIDTH: 60px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula O2 =--(COUNTIFS(B:B,B2,L:L,L2,N:N,N2)>1) O3 =--(COUNTIFS(B:B,B3,L:L,L3,N:N,N3)>1) O4 =--(COUNTIFS(B:B,B4,L:L,L4,N:N,N4)>1) O5 =--(COUNTIFS(B:B,B5,L:L,L5,N:N,N5)>1) O6 =--(COUNTIFS(B:B,B6,L:L,L6,N:N,N6)>1) O7 =--(COUNTIFS(B:B,B7,L:L,L7,N:N,N7)>1) O8 =--(COUNTIFS(B:B,B8,L:L,L8,N:N,N8)>1) O9 =--(COUNTIFS(B:B,B9,L:L,L9,N:N,N9)>1) O10 =--(COUNTIFS(B:B,B10,L:L,L10,N:N,N10)>1) O11 =--(COUNTIFS(B:B,B11,L:L,L11,N:N,N11)>1) O12 =--(COUNTIFS(B:B,B12,L:L,L12,N:N,N12)>1) O13 =--(COUNTIFS(B:B,B13,L:L,L13,N:N,N13)>1) O14 =--(COUNTIFS(B:B,B14,L:L,L14,N:N,N14)>1) O15 =--(COUNTIFS(B:B,B15,L:L,L15,N:N,N15)>1) O16 =--(COUNTIFS(B:B,B16,L:L,L16,N:N,N16)>1) <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

If you not going to filter the data then:

=--(COUNTIFS(A:A,A2,B:B,B2,L:L,L2,N:N,N2)>1)

If you are going to filter the data then:

=--(SUMPRODUCT(SUBTOTAL(3,OFFSET(A$2,ROW(A$2:A$27)-ROW(A$2),0)),--(A$2:A$27=A2),--(F$2:F$27=F2),--(P$2:P$27=P2),--(R$2:R$27=R2))>1)

 

[Sunline]

Thankyou very much . These have cut hours off the workload .
Thankyou .

 

[T. Valko]

Thankyou very much . These have cut hours off the workload .
Thankyou .

You're welcome. Thanks for the feedback!