Need help with conditional formula

[neil_c]

I have a spreadsheet with thousands of rows containing numbers 1-20. I want to highlight specific numbers 3, 5, 8, 9, 15, 20 (7 numbers), etc.
Is there any formula for highlighting a row that has exactly 7 numbers? Do not highlight if any row contains one or two identical numbers. Basically, highlight the line that has the specific numbers 3,5,8,9,15,20.

1. example: 3,5,8,9,15,20,25= match 7number highlight (if possible a column can tell how many numbers matched with that row e.g.7)
3,5,7,16,21,24= do not highlight(even if it has few same number. ( here column can tell how many matched but do not highlight)
2. In addition, is there way if 7 number match in particular row then a coulum can tell ok 7 number matched in rows number 5.

Huge thanks!!

2.

 

[jtakw]

Hi,

I'm not understanding your description clearly, and your screenshot sample for "1." seems incomplete
Your screenshot sample for "2." is missing.

I suggest you upload samples with detailed descriptions of what you expect as results using XL2BB, see my signature for link and info.

So, I'm only addressing the Count of 3,5,8,9,15,20,25 per row:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	3	5	8	9	15	25
2	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
3	11	16	22	25	29	30	32	39	40	41	42	44	52	54	55	65	71	74	78	79		1
Sheet831

Cell Formulas
Range	Formula
V3	V3	=SUM(COUNTIF(A3:T3,{3,5,8,9,15,20,25}))

 

[StephenCrump]

Welcome to the Forum!

Maybe like this (assuming numbers can't be duplicated within any row):

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	3	5	8	9	15	20	25
2	9	10	13	19	20	25	29	34	41	48	49	51	52	53	59	60	68	69	72	78		3
3	8	11	12	15	18	19	21	27	32	33	50	44	47	48	50	57	62	67	69	74		2
4	2	8	14	17	20	22	23	24	36	46	47	58	66	67	71	72	73	74	76	77		2
5	3	7	13	18	26	28	29	32	38	40	47	52	61	62	63	64	65	70	75	79		1
6	3	4	5	8	9	10	12	15	20	23	25	27	29	30	31	38	45	53	66	67		7
7	10	12	17	20	25	29	30	33	34	36	38	39	40	54	57	66	69	74	75	77		2
8	12	16	17	18	19	20	26	36	37	39	45	48	49	56	59	60	65	67	68	69		1
9	1	9	26	28	29	30	32	35	44	46	49	52	56	58	63	65	66	70	71	72		1
10	4	10	11	13	20	26	29	30	31	33	34	41	53	54	59	62	63	71	76	77		1
11	2	2	3	14	15	20	36	40	44	46	50	51	52	53	63	71	73	76	77	79		3
12
13
14	Row number	5
15	in MyData
Sheet1

Cell Formulas
Range	Formula
V2:V11	V2	=SUM(COUNTIF(A2:T2,MyPick))
B13:T13	C13	=IF(C2=B2,9999,"")
B14	B14	=MATCH(7,MMULT(--ISNUMBER(MATCH(A2:T11,MyPick,)),SEQUENCE(COLUMNS(MyData),,1,0)))

Named Ranges
Name	Refers To	Cells
MyData	=Sheet1!$A$2:$T$11	V2, B13:B14
MyPick	=Sheet1!$A$1:$G$1	B14, V2:V11

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:U11	Expression	=SUM(COUNTIF($A2:$T2,MyPick))=7	text	NO

The 5th row is the first occurrence. Let us know if you want a formula for 2nd and subsequent occurrences?

The SEQUENCE part of the formula can be modified if required for earlier versions of Excel.

 

[neil_c]

thank your

Hi,

I'm not understanding your description clearly, and your screenshot sample for "1." seems incomplete
Your screenshot sample for "2." is missing.

I suggest you upload samples with detailed descriptions of what you expect as results using XL2BB, see my signature for link and info.

So, I'm only addressing the Count of 3,5,8,9,15,20,25 per row:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	3	5	8	9	15	25
2	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
3	11	16	22	25	29	30	32	39	40	41	42	44	52	54	55	65	71	74	78	79		1
Sheet831

Cell Formulas
Range	Formula
V3	V3	=SUM(COUNTIF(A3:T3,{3,5,8,9,15,20,25}))

Hi,

I'm not understanding your description clearly, and your screenshot sample for "1." seems incomplete
Your screenshot sample for "2." is missing.

I suggest you upload samples with detailed descriptions of what you expect as results using XL2BB, see my signature for link and info.

So, I'm only addressing the Count of 3,5,8,9,15,20,25 per row:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	3	5	8	9	15	25
2	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
3	11	16	22	25	29	30	32	39	40	41	42	44	52	54	55	65	71	74	78	79		1
Sheet831

Cell Formulas
Range	Formula
V3	V3	=SUM(COUNTIF(A3:T3,{3,5,8,9,15,20,25}))

thank you for your reply.

 

[neil_c]

Welcome to the Forum!

Maybe like this (assuming numbers can't be duplicated within any row):

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	3	5	8	9	15	20	25
2	9	10	13	19	20	25	29	34	41	48	49	51	52	53	59	60	68	69	72	78		3
3	8	11	12	15	18	19	21	27	32	33	50	44	47	48	50	57	62	67	69	74		2
4	2	8	14	17	20	22	23	24	36	46	47	58	66	67	71	72	73	74	76	77		2
5	3	7	13	18	26	28	29	32	38	40	47	52	61	62	63	64	65	70	75	79		1
6	3	4	5	8	9	10	12	15	20	23	25	27	29	30	31	38	45	53	66	67		7
7	10	12	17	20	25	29	30	33	34	36	38	39	40	54	57	66	69	74	75	77		2
8	12	16	17	18	19	20	26	36	37	39	45	48	49	56	59	60	65	67	68	69		1
9	1	9	26	28	29	30	32	35	44	46	49	52	56	58	63	65	66	70	71	72		1
10	4	10	11	13	20	26	29	30	31	33	34	41	53	54	59	62	63	71	76	77		1
11	2	2	3	14	15	20	36	40	44	46	50	51	52	53	63	71	73	76	77	79		3
12
13
14	Row number	5
15	in MyData
Sheet1

Cell Formulas
Range	Formula
V2:V11	V2	=SUM(COUNTIF(A2:T2,MyPick))
B13:T13	C13	=IF(C2=B2,9999,"")
B14	B14	=MATCH(7,MMULT(--ISNUMBER(MATCH(A2:T11,MyPick,)),SEQUENCE(COLUMNS(MyData),,1,0)))

Named Ranges
Name	Refers To	Cells
MyData	=Sheet1!$A$2:$T$11	V2, B13:B14
MyPick	=Sheet1!$A$1:$G$1	B14, V2:V11

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:U11	Expression	=SUM(COUNTIF($A2:$T2,MyPick))=7	text	NO

The 5th row is the first occurrence. Let us know if you want a formula for 2nd and subsequent occurrences?

The SEQUENCE part of the formula can be modified if required for earlier versions of Excel.

boom!! Huge thanks. wonderful this is exactly what I was looking for. Just a quick question: is it possible just to highlight only match numbers rather than the whole row. AND
yes, if you could provide a formula for 2nd and subsequent occurrences for the same matches. (Match numbers are in row 6 so why row 5 came up.)
huge thanks!!

 

[StephenCrump]

Using 365 (it's possible in earlier versions, just different formulae) ...

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	3	5	8	9	15	20	25
2	9	10	13	19	20	25	29	34	41	48	49	51	52	53	59	60	68	69	72	78		3
3	8	11	12	15	18	19	21	27	32	33	50	44	47	48	50	57	62	67	69	74		2
4	2	8	14	17	20	22	23	24	36	46	47	58	66	67	71	72	73	74	76	77		2
5	3	7	13	18	26	28	29	32	38	40	47	52	61	62	63	64	65	70	75	79		1
6	3	4	5	8	9	10	12	15	20	23	25	27	29	30	31	38	45	53	66	67		7
7	10	12	17	20	25	29	30	33	34	36	38	39	40	54	57	66	69	74	75	77		2
8	12	16	17	18	19	20	26	36	37	39	45	48	49	56	59	60	65	67	68	69		1
9	1	3	5	7	8	9	10	14	15	19	20	25	33	58	63	65	66	70	71	72		7
10	4	10	11	13	20	26	29	30	31	33	34	41	53	54	59	62	63	71	76	77		1
11	2	2	3	14	15	20	36	40	44	46	50	51	52	53	63	71	73	76	77	79		3
12
13
14	Row nos	6
15		9
Sheet1

Cell Formulas
Range	Formula
V2:V11	V2	=SUM(COUNTIF(A2:T2,MyPick))
B13:T13	C13	=IF(C2=B2,9999,"")
B14:B15	B14	=LET(x,IF(MMULT(--ISNUMBER(MATCH(MyData,MyPick,)),SEQUENCE(COLUMNS(MyData),,1,0))=COLUMNS(MyPick),ROW(MyData)),FILTER(x,x<>FALSE))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
MyData	=Sheet1!$A$2:$T$11	V2, B13:B14
MyPick	=Sheet1!$A$1:$G$1	B14, V2:V11

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:U11	Expression	=AND(SUM(COUNTIF($A2:$T2,MyPick))=COLUMNS(MyPick),ISNUMBER(MATCH(A2,MyPick,)))	text	NO

 

[neil_c]

Using 365 (it's possible in earlier versions, just different formulae) ...

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	3	5	8	9	15	20	25
2	9	10	13	19	20	25	29	34	41	48	49	51	52	53	59	60	68	69	72	78		3
3	8	11	12	15	18	19	21	27	32	33	50	44	47	48	50	57	62	67	69	74		2
4	2	8	14	17	20	22	23	24	36	46	47	58	66	67	71	72	73	74	76	77		2
5	3	7	13	18	26	28	29	32	38	40	47	52	61	62	63	64	65	70	75	79		1
6	3	4	5	8	9	10	12	15	20	23	25	27	29	30	31	38	45	53	66	67		7
7	10	12	17	20	25	29	30	33	34	36	38	39	40	54	57	66	69	74	75	77		2
8	12	16	17	18	19	20	26	36	37	39	45	48	49	56	59	60	65	67	68	69		1
9	1	3	5	7	8	9	10	14	15	19	20	25	33	58	63	65	66	70	71	72		7
10	4	10	11	13	20	26	29	30	31	33	34	41	53	54	59	62	63	71	76	77		1
11	2	2	3	14	15	20	36	40	44	46	50	51	52	53	63	71	73	76	77	79		3
12
13
14	Row nos	6
15		9
Sheet1

Cell Formulas
Range	Formula
V2:V11	V2	=SUM(COUNTIF(A2:T2,MyPick))
B13:T13	C13	=IF(C2=B2,9999,"")
B14:B15	B14	=LET(x,IF(MMULT(--ISNUMBER(MATCH(MyData,MyPick,)),SEQUENCE(COLUMNS(MyData),,1,0))=COLUMNS(MyPick),ROW(MyData)),FILTER(x,x<>FALSE))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
MyData	=Sheet1!$A$2:$T$11	V2, B13:B14
MyPick	=Sheet1!$A$1:$G$1	B14, V2:V11

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:U11	Expression	=AND(SUM(COUNTIF($A2:$T2,MyPick))=COLUMNS(MyPick),ISNUMBER(MATCH(A2,MyPick,)))	text	NO

THANK you a lot sir.
I'm using 365 but after struggling a lot of time these two formulas are not working :

B13:T13	C13	=IF(C2=B2,9999,"")
B14:B15	B14	=LET(x,IF(MMULT(--ISNUMBER(MATCH(MyData,MyPick,)),SEQUENCE(COLUMNS(MyData),,1,0))=COLUMNS(MyPick),ROW(MyData)),FILTER(x,x<>FALSE))

 

[StephenCrump]

It looks like you don't have the LET function yet. Is your Excel 365 fully up-to-date?

You could also try:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	3	5	8	9	15	20	25
2	9	10	13	19	20	25	29	34	41	48	49	51	52	53	59	60	68	69	72	78		3	Row nos	6
3	8	11	12	15	18	19	21	27	32	33	50	44	47	48	50	57	62	67	69	74		2		9
4	2	8	14	17	20	22	23	24	36	46	47	58	66	67	71	72	73	74	76	77		2
5	3	7	13	18	26	28	29	32	38	40	47	52	61	62	63	64	65	70	75	79		1
6	3	4	5	8	9	10	12	15	20	23	25	27	29	30	31	38	45	53	66	67		7
7	10	12	17	20	25	29	30	33	34	36	38	39	40	54	57	66	69	74	75	77		2
8	12	16	17	18	19	20	26	36	37	39	45	48	49	56	59	60	65	67	68	69		1
9	1	3	5	7	8	9	10	14	15	19	20	25	33	58	63	65	66	70	71	72		7
10	4	10	11	13	20	26	29	30	31	33	34	41	53	54	59	62	63	71	76	77		1
11	2	2	3	14	15	20	36	40	44	46	50	51	52	53	63	71	73	76	77	79		3
Sheet1

Cell Formulas
Range	Formula
X2:X11	X2	=SORT(IF(MMULT(--ISNUMBER(MATCH(MyData,MyPick,)),SEQUENCE(COLUMNS(MyData),,1,0))=COLUMNS(MyPick),ROW(MyData),""))
V2:V11	V2	=SUM(COUNTIF(A2:T2,MyPick))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
MyData	=Sheet1!$A$2:$T$11	V2, X2
MyPick	=Sheet1!$A$1:$G$1	X2, V2:V11

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:U11	Expression	=AND(SUM(COUNTIF($A2:$T2,MyPick))=COLUMNS(MyPick),ISNUMBER(MATCH(A2,MyPick,)))	text	NO

 

[neil_c]

It looks like you don't have the LET function yet. Is your Excel 365 fully up-to-date?

You could also try:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	3	5	8	9	15	20	25
2	9	10	13	19	20	25	29	34	41	48	49	51	52	53	59	60	68	69	72	78		3	Row nos	6
3	8	11	12	15	18	19	21	27	32	33	50	44	47	48	50	57	62	67	69	74		2		9
4	2	8	14	17	20	22	23	24	36	46	47	58	66	67	71	72	73	74	76	77		2
5	3	7	13	18	26	28	29	32	38	40	47	52	61	62	63	64	65	70	75	79		1
6	3	4	5	8	9	10	12	15	20	23	25	27	29	30	31	38	45	53	66	67		7
7	10	12	17	20	25	29	30	33	34	36	38	39	40	54	57	66	69	74	75	77		2
8	12	16	17	18	19	20	26	36	37	39	45	48	49	56	59	60	65	67	68	69		1
9	1	3	5	7	8	9	10	14	15	19	20	25	33	58	63	65	66	70	71	72		7
10	4	10	11	13	20	26	29	30	31	33	34	41	53	54	59	62	63	71	76	77		1
11	2	2	3	14	15	20	36	40	44	46	50	51	52	53	63	71	73	76	77	79		3
Sheet1

Cell Formulas
Range	Formula
X2:X11	X2	=SORT(IF(MMULT(--ISNUMBER(MATCH(MyData,MyPick,)),SEQUENCE(COLUMNS(MyData),,1,0))=COLUMNS(MyPick),ROW(MyData),""))
V2:V11	V2	=SUM(COUNTIF(A2:T2,MyPick))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
MyData	=Sheet1!$A$2:$T$11	V2, X2
MyPick	=Sheet1!$A$1:$G$1	X2, V2:V11

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:U11	Expression	=AND(SUM(COUNTIF($A2:$T2,MyPick))=COLUMNS(MyPick),ISNUMBER(MATCH(A2,MyPick,)))	text	NO

Thanks, both formulas are working now in the web browser. In fact, I have a Uni365 subscription, so I think there's a problem with my office. The sort formula only marking row no.5. however, it should mark row 11 too. But thank you for taking the time to sort it out! Marking this solution solved.

 

[StephenCrump]

The sort formula only marking row no.5. however, it should mark row 11 too.

It works OK for me. Are you sure you've defined your range names correctly?

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2
3			9	10	21	24	28	29	38
4
5			9	10	66	66	21	24	28	29	66	66	38	66	66	66	66	66	66	66	66	66		7		5
6			66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66		0		11
7			66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66		0
8			66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66		0
9			66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66		0
10			66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66		0
11			9	10	66	66	21	24	28	29	38	66	66	66	66	66	66	66	66	66	66	66		7
12			66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66		0
13			66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66		0
14			66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66		0
Sheet1

Cell Formulas
Range	Formula
Z5:Z14	Z5	=SORT(IF(MMULT(--ISNUMBER(MATCH(MyData,MyPick,)),SEQUENCE(COLUMNS(MyData),,1,0))=COLUMNS(MyPick),ROW(MyData),""))
X5:X14	X5	=SUM(COUNTIF(C5:V5,MyPick))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
MyData	=Sheet1!$C$5:$V$14	X5, Z5
MyPick	=Sheet1!$C$3:$I$3	Z5, X5:X14

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C5:W14	Expression	=AND(SUM(COUNTIF($C5:$V5,MyPick))=COLUMNS(MyPick),ISNUMBER(MATCH(C5,MyPick,)))	text	NO