Count IF based on date

[sudanaditya]

Hello All - I have a scenario where I do a count IF based  on below sample data  table

1. Count IFS - Priority (P1,P2..) based on Open date & Closed date with situation = Issues ( should exclude action)

Trend I am trying to build

Date	P1 Open	P2 Open	P3 Open	P4 Open	P5 Open	GLC Open	P1 Close	P2 Close	P3 Close	P4 Close	P5 Close	GLC Close
25-Nov-18	0	0	0	0	0	0	0	2	3	0	0	5
2-Dec-18	0	0	0	0	0	0	0	2	3	0	0	5
9-Dec-18	0	12	8	1	0	20	0	5	3	0	0	8
16-Dec-18	0	21	16	1	1	37	0	12	10	0	0	22
23-Dec-18	0	24	26	2	1	50	0	17	14	0	0	31
30-Dec-18	0	28	27	2	1	55	0	20	16	0	0	36
6-Jan-19	0	34	32	4	3	66	0	25	23	0	0	48
13-Jan-19	0	41	41	4	4	82	0	30	27	1	0	57
20-Jan-19	0	52	49	7	5	101	0	44	36	1	0	80
27-Jan-19	0	72	59	7	5	131	0	56	46	1	0	102
3-Feb-19	0	88	69	11	5	157	0	75	54	3	0	129
10-Feb-19	0	94	82	13	8	176	0	88	66	6	3	154
17-Feb-19	0	97	92	15	8	189	0	92	76	6	3	168
24-Feb-19	0	102	100	17	8	202	0	96	81	6	3	177
3-Mar-19	0	104	100	18	8	204	0	98	81	6	3	179
10-Mar-19	0	118	111	20	8	229	0	111	96	6	3	207
17-Mar-19	0	139	121	21	8	260	0	118	103	6	3	221
24-Mar-19	1	153	133	22	9	287	1	140	112	6	3	253
31-Mar-19	1	169	156	24	9	326	1	150	129	7	4	280

Data Table

Issue ID	Project	Situation	Description	Issue Status	Priority	Due Date	Open Date	Closed Date
90919	4512 - RA Models - Secondary Launch	Issue	Incorrect warehouse return for RA-2051-DPL and RA-0078-ACC	In Process	P4		12/6/2018 0:00
91914	4512 - RA Models - Secondary Launch	Action	Ability of Global Trax to consolidate SOPO and IRISO orders	In Process	P3	4/3/2019 0:00	1/8/2019 0:00
92349	4512 - RA Models - Secondary Launch	Issue	WSS OA is showing Weird values under configuration	Reported	P5		1/18/2019 0:00
94441	4512 - RA Models - Secondary Launch	Issue	Pudong sales office is unable to receive the items because there is no RA model displayed in RNI Packing slip	In Process	P2	3/21/2019 0:00	2/22/2019 0:00
95190	4512 - RA Models - Secondary Launch	Issue	SMMC_GSOF Promise Date is not update based on SSD + PPLT	Closed	P4	3/25/2019 0:00	3/7/2019 0:00	3/7/2019 0:00

[sudanaditya]

Any help is appreciated

[sudanaditya]

Hello -Let me create a simple table and explain it . In the below table the we are conting Priority for a date if the Issue category = Issue. Please find the Result table below as how it should display .

Thanks in advance

sudan

Open date	Issue Category	Priority
3/4/2019	Action	P1
3/4/2019	Issue	P1
3/4/2019	Issue	P2
3/5/2019	Issue	P2
3/6/2019	Issue	P3
Please note Issue category -Action is excluded
Result
Opendate	P1	P2	P3	P4
3/4/2019	1	1
3/5/2019	1
3/6/2019			1
3/7/2019

[TeigeGao]

Hi @sudanaditya ,

Sorry, I think I can't understand your requirement well. Could you please share the expected result to us? 

Best Regards,

Teige