If And Or conditional formatting .

Sunline

Well-known Member
Joined
Oct 6, 2007
Messages
675
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
FPRS
1DateDistR#Result
213/01/201222001
313/01/201222002
413/01/201211503
513/01/20121150101
613/01/20121150101
713/01/20121150101
813/01/2012160010
914/01/201221005
1014/01/2012210071
1114/01/2012210071
1218/01/2012140010
1320/01/201216002
1420/01/2012160081
1520/01/2012160081
1620/01/2012160081
1720/01/2012160081
1821/01/201213357
1921/01/201212003
2027/01/2012140011
2129/01/201221001
221/02/201248006
233/02/2012480061
243/02/2012480061
254/02/201214005
264/02/201214001
278/02/201220003
Sheet1
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

FPRS
1DateDistR#Result
213/01/201222001
313/01/201222002
413/01/201211503
513/01/20121150101
613/01/20121150101
713/01/20121150101
813/01/2012160010
914/01/201221005
1014/01/2012210071
1114/01/2012210071
1218/01/2012140010
1320/01/201216002
1420/01/2012160081
1520/01/2012160081
1620/01/2012160081
1720/01/2012160081
1821/01/201213357
1921/01/201212003
2027/01/2012140011
2129/01/201221001
221/02/201248006
233/02/2012480061
243/02/2012480061
254/02/201214005
264/02/201214001
278/02/201220003

<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

Well-known Member
Joined
Oct 6, 2007
Messages
675
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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

Well-known Member
Joined
Oct 6, 2007
Messages
675

ADVERTISEMENT

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

Well-known Member
Joined
Oct 6, 2007
Messages
675
Sorry forgot this sample sheet .
Excel Workbook
ABLNOP
1MeetDateDistR#ResultMy error
2Teaw23/05/2012115080
3Tren26/05/2012120021Here
4Elle26/05/2012120021Here
5Elle26/05/2012140030
6Puke31/05/2012120071
7Puke31/05/2012120071
8Elle2/06/2012120010
9Hast7/06/2012140080
10Tera9/06/2012120040
11Elle16/06/2012120020
12Elle16/06/2012140090
13Taur23/06/2012120011
14Taur23/06/2012120011
15Taur23/06/2012140020
16Taur23/06/2012140060
Sheet1
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623

ADVERTISEMENT

Sorry forgot this sample sheet .
Sheet1

ABLNOP
1MeetDateDistR#ResultMy error
2Teaw23/05/2012115080
3Tren26/05/2012120021Here
4Elle26/05/2012120021Here
5Elle26/05/2012140030
6Puke31/05/2012120071
7Puke31/05/2012120071
8Elle2/06/2012120010
9Hast7/06/2012140080
10Tera9/06/2012120040
11Elle16/06/2012120020
12Elle16/06/2012140090
13Taur23/06/2012120011
14Taur23/06/2012120011
15Taur23/06/2012140020
16Taur23/06/2012140060

<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
CellFormula
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

Well-known Member
Joined
Oct 6, 2007
Messages
675
Thankyou very much . These have cut hours off the workload .
Thankyou .
 

Watch MrExcel Video

Forum statistics

Threads
1,123,481
Messages
5,601,918
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top