SUMPRODUCT formlua works inside the sheet but not in conditional formatting

[Ramadan]

Guys, while I'm testing a SOMPRODUCT formula in the excel sheet it works fine and gives me ture or false result based on the range values , but when I try to use the same formula in conditional formatting I get errors evrytime, please let me know what's I'm doing wrong - here is the formula

=SUMPRODUCT(('Stop Work'!$D$8:$D$500=$C11)*('Stop Work'!$K$8:$K$500="Open")*('Stop Work'!$C$8:$C$500=$B$2))*($T$10:$T500=$B$3)

it was working good in conditional formatting before I add the last condition which is this part *($T$10:$T500=$B$3)

 

[etaf]

you have T10
should be T8
to match ranges

try
*($T$8:$T500=$B$3)

 

[Ramadan]

you have T10
should be T8
to match ranges

try
*($T$8:$T500=$B$3)

Thanks Mr. Etaf, maddly it didn't give error now even with keeping it the same *($T$10:$T500=$B$3) don't know how !! but i didn't apply the condition as I need
look, will explain what i need, i was working with this formula =SUMPRODUCT(--('Stop Work'!$D$8:$D$500=$C10)*('Stop Work'!$K$8:$K$500="Open")*('Stop Work'!$C$8:$C$500=$B$2)) the to check if C10 in this sheet = any cell in stopwork sheet column "D" and "K" in stopworksheep = "Open" + also check if "C" in stopworksheet = $B$2 in this sheet then change the row color
till this part i was working perfectly but i want to add another condition says to check also if "T" in this sheel also in blank... and my table in the active cheet start with T10 thats why I put $T$10

 

[etaf]

not following , as
*($T$10:$T500=$B$3) is NOT in the same sheet and starts at row 10

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[Ramadan]

not following , as
*($T$10:$T500=$B$3) is NOT in the same sheet and starts at row 10

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

I hope this screenshot can help to understand what I need
as you can see sheets name at the bottom and i apply this condition to all sheets based on data from the last one "stopwork"
image one is the sheet I'm oworking on and table starts with row #10 and as you can see when I wrote "1" in T11 still the row colored in red while it shouldn't because B3 in empty *($T$10:$T500=$B$3)
image 2 is the stopwork sheet table starts with row #8

 

[etaf]

you would need a different rule to stop that , as i say you are using a different sheet and row range in sumproduct , i'm not sure it can do that

Are you sure the orginal formula is working OK
=SUMPRODUCT(('Stop Work'!$D$8:$D$500=$C11)*('Stop Work'!$K$8:$K$500="Open")*('Stop Work'!$C$8:$C$500=$B$2))

$C11 , seems a strange , cell to reference

So what do you want to happen with
$T$10:$T500=$B$3)
you could possibly use AND to include that condition with maybe a countif()

 

[Ramadan]

you would need a different rule to stop that , as i say you are using a different sheet and row range in sumproduct , i'm not sure it can do that

Are you sure the orginal formula is working OK
=SUMPRODUCT(('Stop Work'!$D$8:$D$500=$C11)*('Stop Work'!$K$8:$K$500="Open")*('Stop Work'!$C$8:$C$500=$B$2))

$C11 , seems a strange , cell to reference

So what do you want to happen with
$T$10:$T500=$B$3)
you could possibly use AND to include that condition with maybe a countif()

yes this formual is working perfectly till this end
=SUMPRODUCT(('Stop Work'!$D$8:$D$500=$C10)*('Stop Work'!$K$8:$K$500="Open")*('Stop Work'!$C$8:$C$500=$B$2)) - sorry for c11 it's C10 but was testing it on another row

till this end it's very good and what I need is only to add one more condition which is to ckeck if "T" in sheet1 ( the sheet which I'm working on) is empty or blank so I left B3 empty and made the condition like this ( $T$8:$T500=$B$3) and it works but with conflict as i explained - when I put any data in "T" nothing change but when I put any data in B3 it works don't know why

so, if I want to add AND to this formula
=SUMPRODUCT(('Stop Work'!$D$8:$D$500=$C10)*('Stop Work'!$K$8:$K$500="Open")*('Stop Work'!$C$8:$C$500=$B$2))
and to add this condition ( $T$8:$T500=$B$3) how should it be

thank you again

 

[etaf]

you could just add an AND

=AND( SUMPRODUCT(('Stop Work'!$D$8:$D$500=$C10)*('Stop Work'!$K$8:$K$500="Open")*('Stop Work'!$C$8:$C$500=$B$2)) , $T10=$B$3)
So now its only true if both conditions are MET

SO when it gets to your example image T11 , as that is NOT blank which B3 is , then it will be false

Assuming your Conditional formatting range , is starting at row 10

 

[Ramadan]

AUTHs.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
7	S	Unit No.	Owner Name	Phone No.	Auth. Date	Authorized Personnel								H.O	INS	Work Permit		Remarks
8						Name	Phone No.	Name	Phone No.	Name	Phone No.	Name	Phone No.	Report	Cheque	Status	Exp. Date
9	Column1	L	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13	Column133	Column132	Column14	Column15	Column152	Column16
10	1	08 NEW 37	السيد احمد محمد ابراهيم	01288101738	16.01.23	عبد الرحمن لبيب	01002002601								OK
11	2	10 NEW 09	مصطفى يحيى محمد	01118077777	17.01.22	عبد الله طارق اسماعيل	01099511271							N/A	OK	OK	17.03.22
12	3	104 NEW 02	احمد حلمى طولان	01110010074	11.04.22	محمد احمد وحيد	01063783019							N/A	OK	OK	GL
13	4	20 NEW 25	عماد عبد العال الريدى	01223609132	03.08.23	احمد حسام ابراهيم	01556721444							N/A	OK	OK	02.10.23
14	5	14 NEW 50	احمد سمير محمد نور سالم	01097772310	02.06.21	اسلام وحيد ابراهيم	01110006318								OK
15	6	21 NEW 27	سامر فايز واصف	01001092826	04.07.23	جمال محمد العطار	01001800180	احمد جمال محمد	01009990727					N/A	OK	OK	03.09.23
16	7	19 NEW 449	عادل فاروق مهنى	01222151117	14.09.20	شريف الهوارى	01000366337	ابراهيم محمد	01118783067
17	8	19 NEW 479	احمد على احمد عبد الرحمن	01001410165	06.07.21	شريف على	01111099988
18	9	20 NEW 478	احمد هشام عبد الغنى	01000008904	31.08.22	محمد شجاع محمد على	01121503701	احمد محمد فاخورى	01113317283	علاء الدين عبد الباقى	01028647400	حسين ناصر + 4 اخرين	01001563180
19	10	20 NEW 412	نهى مجدى احمد	01000033503	11.12.22	عمر شريف محمود عطيه	01004995522
20	11	29 NEW 28	مصطفى محمد ابراهيم	01124044409	26.11.21	احمد اسماعيل ممدوح	01003480098							N/A	GL	OK	05.02.22
21	12	72 NEW 15	هدى مصطفى محمد سعودى	01222117780	16.03.22	محمد عونى سيد	01126482294	حميد عونى سيد احمد	01006256608					N/A	OK	OK	GL
22	13	89 NEW 26	عبد العظيم عصام الدين عبد العظيم	01002109352		سامر لطفى حبيب	01220781311	جون عطبيه عبد المسيح	01019191469	احمد اشرف احمد	01155006820	وحيد ثابت	01001803440	N/A	GL	OK	GL
23	14	39 NEW 179	محمد عبد السلام الشافعى	01001801113		احمد مسلم الدين عبد المط	01000015338
24	15	07 NEW 461	محمد خالد عبد المجيد البارونى	01222171371	02.04.24	محمد نادر درويش	01002661882	عبد الله عبد القادر ع الهادى	01228333888	عمر لطفى	01064737648			Finished	OK	OK	22.06.24
25	16	47 NEW 11	كريم محمود حسن الطوخى	01006149110	22.02.23	عادل عبده خالد عفيفى	01015888312								OK
26	17	100 NEW 29	كريم عوض محمد	01005660404	10.12.21	مؤمن عبد العظيم	01006051644	منه القاضى	01001212982	ياسر عبد الله حامد	01226141175			N/A	GL	OK	GL
27	18	11 NEW 31	علاء عادل مراد ذكرى	01222102690	23.03.22	مروج وحيد رأفت	01004837778	حسن محمد حسن	01100690554					N/A	GL	OK	GL
28	19	11 NEW 31	علاء ذكرى	01222102690	08.02.22	هشام محمد مجدى	01227417043	فرح محمد عبد السلام	01223344567					N/A	GL	OK	GL
29	20	54 NEW 02	امل سالم محمود الوكيل	01000766725	16.07.20	خالد محمود عبد السميع	01112522889	محمد مسعد	01115544010
30	21	55 NEW 26	احمد صلاح جادو	01223125906	08.11.21	احمد عبد المعطى حسن	01069669919
31	22	119 NEW 111	محمد محمود احمد عبد الحافظ	01000860952	30.01.23	ياسر عبد العال	01012030455							N/A	GL	OK	GL
32	23	58 NEW 71	محمد احمد عبد الوهاب احمد	+8687641594	30.08.22	اسلام توفيق ابو شادى	01004572373	محمد على حامد	01093167187	خالد سيد عبد الرحمن	01149894119				OK			الغاء بامر المالك شخصيا 26/12	X
33	24	63 NEW 44	رنا ماهر محب استينو	01286206655	20.02.22	ابراهيم محمد مصيلحى	01275513688
34	25	123 NEW 01	طاهر يحيى محمد	01000184484		محمد مصطفى محمد		عطوه سيد حسن						N/A	GL	OK	GL
35	26	123 NEW 119	مصطفى على عبد اللطيف محمد	01227459223	22.01.24	محمد احمد حفنى	01285030555							Clear	OK	OK	22.03.24
36	27	129 NEW 131	ايهاب محمد فتحى الاشقر	01222140970	11.01.23	رضا عبد المقصود	01221009999	مصطفى محمد مصطفى	01281555313	احمد محمد عطا	01289164446			N/A	GL	OK	GL
37	28	13 NEW 469	نهال عصام ابراهيم	01001674007	17.04.24	على احمد السيد سالم	01006279816	ايهاب محمد احمد السيد	01092916521	سيف طلعت	01006477799			Clear	OK	OK	17.06.24
38	29	131 NEW 12	رانيا احمد كمال عبيد	+97455140375	17.01.21	احمد كمال عبد الحميد	01222396627	احمد شعبان بدير	01110617830					N/A	OK	OK	GL
39	30	81 NEW 35	شريف جابر فرج حشاد	+971507889395	11.05.22	محمد حسين عبد الحميد	01093707120	طارق عبد المحسن	01007583846
40	31	132 NEW 13	محمود صلاح الدين بسيونى	01008666630	04.04.24	محمد عبد الحميد عبد الله	01001840952							Finished	OK	OK	07.07.24
Ivory

Cell Formulas
Range	Formula
B10:B40	B10	=IF([@L]<>"",ROW()-ROW($B$9),"")
Q10:Q40	Q10	=IF($R10<>"","OK","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B10:T326	Expression	=SUMPRODUCT(('Stop Work'!$D$8:$D$500=$C10)*('Stop Work'!$K$8:$K$500="Open")*('Stop Work'!$C$8:$C$500=$B$2))	text	NO
C10:C326	Expression	=AND(ISBLANK($P10),$R10<>"",$T10="")	text	NO
C10:C326	Expression	=$T10<>""	text	NO
C10:C326	Expression	=AND($D$3<>"",ISNUMBER(SEARCH($D$3,$G10&$I10&$K10&$M10)))	text	NO
C10:C326	Expression	=(AND($P10<>"",$E10<>"",$R10="",$T10=""))	text	NO
C10:C326	Expression	=(AND($P10<>"",$E10="",$R10="",$T10=""))	text	NO
C10:C326	Expression	=AND(ISBLANK($P10),ISBLANK($R10),ISBLANK($T10))	text	NO
C10:C326	Expression	=(AND($P10<>"",$R10<>"",($T10="")))	text	NO
B10:T326	Expression	=AND(ISBLANK($P10),$R10<>"",$T10="")	text	NO
B10:T326	Expression	=$T10<>""	text	NO
B10:T326	Expression	=AND($D$3<>"",ISNUMBER(SEARCH($D$3,$G10&$I10&$K10&$M10)))	text	NO
B10:T326	Expression	=(AND($P10<>"",$E10<>"",$R10="",$T10=""))	text	NO
B10:T326	Expression	=(AND($P10<>"",$E10="",$R10="",$T10=""))	text	NO
B10:T326	Expression	=AND(ISBLANK($P10),ISBLANK($R10),ISBLANK($T10))	text	NO
B10:T326	Expression	=(AND($P10<>"",$R10<>"",($T10="")))	text	NO
B11:T323	Expression	=AND(ISBLANK($P11),$R11<>"",$T11="")	text	NO
B11:T323	Expression	=$T11<>""	text	NO
B11:T323	Expression	=AND($D$3<>"",ISNUMBER(SEARCH($D$3,$G11&$I11&$K11&$M11)))	text	NO
B11:T323	Expression	=(AND($P11<>"",$E11<>"",$R11="",$T11=""))	text	NO
B11:T323	Expression	=(AND($P11<>"",$E11="",$R11="",$T11=""))	text	NO
B11:T323	Expression	=AND(ISBLANK($P11),ISBLANK($R11),ISBLANK($T11))	text	NO
B11:T323	Expression	=(AND($P11<>"",$R11<>"",($T11="")))	text	NO

AUTHs.xlsm
	B	C	D	E	F	G	H	I	J	K
6	S	NH	Unit No.	Description	Issued By Eng.	Stop Date	Resume Date	Issued By Eng.	Remarks	Status
7	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10
8	1	NH03	V 111 NEW V 95	تركيب برجوله فى اللاند سكيب	Eng. Ahmed Anwer	14.04.23	30.04.23	Eng. Ahmed Anwer		Done
9	2	NH05	RW09 - 391	modifications without any approvals	Eng. Ahmed Anwer	04.06.23	06.06.23	Eng. Ahmed Anwer		Done
10	3	NH04	VB - 05	مخالفة لون الوجهه	Eng. Abdullah Mohsen	04.06.23	08.06.23	Eng. Abdullah Mohsen		Done
11	4	NH05	RT09 - 11	Modifications	Eng. Ahmed Anwer	02.05.23	21.06.23	Eng. Ahmed Anwer		Done
12	5	NH05	TV10 - 56A	client removed the corner Louver	Eng. Ahmed Anwer	18.06.23	21.06.23	Eng. Ahmed Anwer		Done
13	6	NH07	AV 04A -G1	the client to remove the opened windows	Eng. Ahmed Anwer	21.06.23	21.06.23	Eng. Ahmed Anwer		Done
14	7	NH05	TV10 - 37A		Eng. Ahmed Anwer	01.12.24	21.06.23	Eng. Ahmed Anwer		Done
15	8	NH02	F4 - 04	Building wall in the garden	Eng. Abdullah Mohsen	25.06.23	18.07.23	Eng. Abdullah Mohsen		Done
16	9	NH04	RW09A - 35	making a partition	Eng. Abdullah Mohsen	25.05.23	24.07.23	Eng. Abdullah Mohsen		Done
17	10	NH04	LS WF 23R	تكسير في جدار الادوار الخاص بالوحدة	Eng. Abdullah Mohsen	12.08.23	12.08.23	Eng. Abdullah Mohsen		Done
18	11	NH04	RW08 - 50	تكسير بالجدران الدور الاول	Eng. Abdullah Mohsen	15.07.23	23.08.23	Eng. Abdullah Mohsen		Done
19	12	NH04	RW10 - 100	violation in pergola	Eng. Abdullah Mohsen	22.08.23	23.08.23	Eng. Abdullah Mohsen		Done
20	13	NH04	RW09 - 55	تكسير وتعديلات	Eng. Abdullah Mohsen	09.08.23	27.08.23	Eng. Abdullah Mohsen		Done
21	14	NH04	RT10 - 48	تكسير وعمال فتحات بالدور الاول	Eng. Abdullah Mohsen	20.08.23	05.09.23	Eng. Abdullah Mohsen	Verbal	Done
22	15	NH04	RT09 - 149	Damage	Eng. Abdullah Mohsen	10.09.23	11.09.23	Eng. Abdullah Mohsen		Done
23	16	NH02	F405	تركيب أرضية خشبية وبرجولة خشبية	Eng. Abdullah Mohsen	06.08.23	07.10.23	Eng. Abdullah Mohsen		Done
24	17	NH01	156 NEW 46	بناء بالطوب ) اعلى الفيلا	Eng. Abdullah Mohsen	13.10.23	15.10.23	Eng. Abdullah Mohsen		Done
25	18	NH01	34 NEW 121	لحين تركيب حاجز اخضر على الفيلا من الخارج	Eng. Abdullah Mohsen	17.10.23	17.10.23	Eng. Abdullah Mohsen		Done
26	19	NH07	AV23B - G3	شاتر	Eng. Ahmed Anwer	15.10.23	22.10.23	Eng. Ahmed Anwer		Done
27	20	NH01	79 R3	برجولا على الروف	Eng. Ashraf Sokar	24.10.23	31.10.23	MohamedAnwer	till 2/11 to remove violation	Done
28	21	NH07	AV 10A - P3	Roof pergola violation	Eng. Ahmed Anwer	29.10.23	01.11.23	Eng. Ahmed Anwer	to remove violation	Done
29	22	NH01	15 NEW 43	تركيب رخام بواجهة الفيلا	Eng. Abdullah Mohsen	23.10.23	06.11.23	Eng. Abdullah Mohsen		Done
30	23	NH01	01 NEW 107	لبدء عمل ممشي لفيلا	Eng. Abdullah Mohsen	03.12.23	06.11.23	Eng. Abdullah Mohsen	got approval	Done
31	24	NH04	RW09A - 17	حفر لعمل حمام سباحة بدون موافقات	Eng. Abdullah Mohsen	06.11.23	07.11.23	Eng. Abdullah Mohsen	ما عدا العمل بحمام السباحه	Done
32	25	NH07	AV 22B - G2	un approved pergola and a glass Fence	Eng. Ahmed Anwer	07.11.23	07.11.23	Eng. Ahmed Anwer	لحين حصول العمل على الموافقات	Done
33	26	NH05	TV09A - 55B	installed a steel slab & Modification on the External Elevation without approvals	Eng. Ahmed Anwer	06.11.23	08.11.23	Eng. Ahmed Anwer		Done
34	27	NH04	RT08 - 143	CAR SHADE	Eng. Abdullah Mohsen	07.11.23	13.11.23	Eng. Abdullah Mohsen		Done
35	28	NH05	TV09A - 55B	structure modification & Elevation Modification	Eng. Ahmed Anwer	09.11.23	23.11.23	MohamedAnwer	till getting apprivals	Done
36	29	NH04	V-B - 34	( تكسير ) داخل الفيلا	Eng. Abdullah Mohsen	12.10.23	26.11.23	Eng. Ahmed Anwer	in approved modifications only	Done
37	30	NH02	F4 - 04	تغير شكل باب الوحدة بتركيب تجاليد خشاب للباب والحائط المحيط له	Eng. Abdullah Mohsen	29.11.23	30.11.23	MohamedAnwer	untill taking approvals	Done
38	31	NH04	RW 09A-36	حفر حمام سباحة	Eng. Abdullah Mohsen	18.11.23	05.12.23	Eng. Abdullah Mohsen	As per Eng. Asraf Sokar	Done
39	32	NH04	RT08 - 76	تكسير بالحوائط داخل الفيلا	Eng. Abdullah Mohsen	18.11.23	06.12.23	Eng. Abdullah Mohsen	got approval	Done
40	33	NH02	B6 - 32	تسريب مياه على B622	Eng. Abdullah Mohsen	03.12.23	06.12.23	Eng. Abdullah Mohsen		Done
41	34	NH05	TV08 - 59B	the locations of the compressors is rejected	Eng. Ahmed Anwer	06.12.23	06.12.23	Eng. Ahmed Anwer	As per higher management	Done
42	35	NH03	22 NEW 476	بدء حفر حمام سباحه	Eng. Ahmed Anwer	15.12.23	07.12.23	Eng. Ahmed Anwer		Done
43	36	NH03	94 NEW 20	تركيب ( مثلث ايرس اعلى الفيلا ) بالالوميتال والزجاج.	Eng. Mostafa Mahmoud	02.10.23	11.12.23	Eng. Ahmed Anwer		Done
44	37	NH06	RT10 - 94	demoloshing	Eng. Ahmed Anwer	28.11.23	11.12.23	Eng. Ahmed Anwer		Done
Stop Work

Cell Formulas
Range	Formula
B8:B44	B8	=IF($C8<>"",ROW()-ROW($B$7),"")
K8:K44	K8	=IF(ISBLANK($H8),"Open","Done")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K8:K373	Expression	=$H8=""	text	NO
C22	Expression	=$C22="NH03"	text	NO
D22:K22	Expression	=AND($E$2<>"",ISNUMBER(SEARCH($E$2,$E22)))	text	NO
C22	Cell Value	="NH07"	text	NO
D22:K22	Expression	=$H22<>""	text	NO
C22	Cell Value	="NH06"	text	NO
C22	Cell Value	="NH05"	text	NO
C22	Cell Value	="NH04"	text	NO
C22	Cell Value	="NH02"	text	NO
C22	Cell Value	="NH01"	text	NO
C22	Cell Value	="NH06"	text	NO
C22	Cell Value	="NH05"	text	NO
C22	Cell Value	="NH04"	text	NO
C22	Cell Value	="NH02"	text	NO
C22	Cell Value	="NH01"	text	NO
C8:C373	Expression	=$C8="NH03"	text	NO
D373,D8:K372,F373:K373	Expression	=AND($E$2<>"",ISNUMBER(SEARCH($E$2,$E8)))	text	NO
C8:C373	Cell Value	="NH07"	text	NO
D373,D8:K372,F373:K373	Expression	=$H8<>""	text	NO
I8:I373	Expression	=ISNUMBER(SEARCH($E$2,$D:$E,1))	text	NO
C44	Cell Value	="NH06"	text	NO
C44	Cell Value	="NH05"	text	NO
C44	Cell Value	="NH04"	text	NO
C44	Cell Value	="NH02"	text	NO
C44	Cell Value	="NH01"	text	NO
C8:C373	Cell Value	="NH06"	text	NO
C8:C373	Cell Value	="NH05"	text	NO
C8:C373	Cell Value	="NH04"	text	NO
C8:C373	Cell Value	="NH02"	text	NO
C8:C373	Cell Value	="NH01"	text	NO
C43,C49	Cell Value	="NH06"	text	NO
C43,C49	Cell Value	="NH05"	text	NO
C43,C49	Cell Value	="NH04"	text	NO
C3:C1048576	Cell Value	="NH06"	text	NO
C3:C1048576	Cell Value	="NH05"	text	NO
C3:C1048576	Cell Value	="NH04"	text	NO
C3:C1048576	Cell Value	="NH02"	text	NO
C3:C1048576	Cell Value	="NH01"	text	NO

 

[Ramadan]

I got it guys like this =SUMPRODUCT(('Stop Work'!$D$8:$D$500=$C10)*('Stop Work'!$K$8:$K$500="Open")*('Stop Work'!$C$8:$C$500=$B$2)*($T10=""))

it was so simple but i ddin't notice that ---- it works now thank you anyway for your usual help