Conditional Formatting for multiple conditions

[Farooqui Noor]

Dear Sir,

I have some data in Excel Sheet as per following details
In Column A :- Sr.No.
In Column B :- Code No. of various Institutes (e.g. 1802, 1381, 1586, 1324, 3353, 2832, 5421, 1216 etc) Each code repeats number of time in column B
In Column C :- Question Paper Code of various Subjects (e.g. 17501, 22564, 17216, 17530 etc) Each code repeats number of time in column C infront of different Institute codes
In Column D :- Total Number of Answer Books received. (e.g. 63, 3, 18, 15 etc)
In Column E :- Date of issue of Answer Books to Assessors (e.g 21/11/2019, 22/11/2019, 23/11/2019 etc)

In other columns I was prepared a table (as shown in image) and calculate the total number of answer books received with respect to concerned institute code and Question paper code by using a formula =SUMIFS($D$3:$D$214,$B$3:$B$214,"1586",$C$3:$C$214,"22564")

what actually I wants
when I will enter the date of issue (e.g 21/11/2019, 22/11/2019) in column E, at the same time the total number of answer books (which i was getting in other table by using above formula) will also filled by green colour by using conditional formatting with respect to concerned institute code and paper code.
hoping for solution.
Thanks

 

[Farooqui Noor]

Please go through second posted image for better clarification.

 

[Snakehips]

@Farooqui Noor
Try this.....

Firstly I think you can simplify your table formula by using cell references rather than the "" values
So formula in I6 ..... =SUMIFS($D$3:$D$214,$B$3:$B$214,I$4,$C$3:$C$214,$H6) and drag down and across.
Then for the conditional formatting, select I6:P214 > Cond Formatting > Formula >

=SUMPRODUCT(($C$3:$C$214=$H6)*($B$3:$B$214=I$4)*($E$3:$E$214>0))>0

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2	SR No.	Inst	Paper	Ans Books	Date
3	1	1802	22564	63
4	2	1381	22564	3				Inst	1802	1381	1586	1324	3353	2832
5	3	1586	22564	18	28/11/2019		SR	Paper
6	4	1324	22564	15			1	17603	0	0	0	0	0	0
7	5	3353	22564	64			2	22502	0	0	0	0	0	0
8	6	2832	22564	2			3	17501	0	0	0	0	0	0
9	7	2332	22564	67			4	22564	63	3	18	15	64	2
10	8	1589	22564	25			5	17216	0	0	0	0	0	0
11	9	1512	22564	27			6	17502	0	0	0	0	0	0
12	10	1577	22564	50			7	17530	38	12	17	10	45	7
13	11	1644	22564	77			8		0	0	0	0	0	0
14	12	1671	17530	1			9		0	0	0	0	0	0
15	13	1802	17530	38			10		0	0	0	0	0	0
16	14	1381	17530	12			11		0	0	0	0	0	0
17	15	1586	17530	17			12		0	0	0	0	0	0
18	16	1324	17530	10	22/11/2019		13		0	0	0	0	0	0
19	17	3353	17530	45			14		0	0	0	0	0	0
20	18	2832	17530	7			15		0	0	0	0	0	0
Sheet6

Hope that helps

 

[Farooqui Noor]

Thank You very much sir
Its working
hoping favourable reply in future also
thanks once again