Countifs or Pivot table

[waqasbutt]

Hello All,

Need your help, I am trying to use countifs function to get the results (refer below second table) but countif does not works.
I want to count how many issues in a month and results as well. As each month, different issues occurs and I want month count.

I tried Pivot table but there are two issues:

First, if I ungroup it combine months results e.g Dec 18 has 10 records and Dec 19 has 5, it shows me 15.
Second if I do not ungroup them it shows me records with respective months but for results column I only need records with Pass. E.g in same pivot table, I need total records for issues but for results only having Pass. I need this for graph where record will be bar and results will be trend line (ideally I need %).

I tried countifs because it is much flexible where I can search based on specific column and but somehow it does not works.

Any help will be appreciated.

DATA
-----

Item	Date	Results
Issue1	15/12/2019 17:49:57	Pass
Issue2	15/12/2019 17:49:57	Pass
Issue3	14/01/2019 18:41:36	Pass
Issue4	14/01/2019 19:36:39	FAIL
Issue5	15/01/2019 9:18:06	Pass
Issue6	15/01/2019 9:37:55	Pass
Issue7	15/01/2019 9:53:06	Pass
Issue8	15/02/2019 9:51:50	Pass
Issue9	15/03/2019 11:51:52	Pass
Issue10	15/03/2019 11:59:55	Pass
Issue11	15/04/2019 12:27:17	Pass
Issue12	15/05/2019 9:08:04	FAIL
Issue13	15/06/2019 9:13:19	FAIL
Issue14	15/12/2018 9:31:44	Pass
Issue15	15/12/2018 9:31:44	Pass

Results I am looking for;

Month	Issues	Results (ONLY PASS)
Dec-18	2	2
Jan-19	5	4
Feb-19	1	1
Mar-19	2	2
Apr-19	1	1
May-19	1	0
Jun-19	1	0
Dec-19	2	2

Regards,
Waqas

 

[DanteAmor]

Try this

In the column of the month put the day 1 of each month ex: 1/Jan/2019 in format you can change to Jan/2019

Dante Amor
	A	B	C	D	E	F	G
1	Item	Date	Results		Month	Issues	Results (ONLY PASS)
2	Issue1	15/12/2019 17:49	Pass		01-dic-18	2	2
3	Issue2	15/12/2019 17:49	Pass		01-ene-19	5	4
4	Issue3	14/01/2019 18:41	Pass		01-feb-19	1	1
5	Issue4	14/01/2019 19:36	FAIL		01-mar-19	2	2
6	Issue5	15/01/2019 09:18	Pass		01-abr-19	1	1
7	Issue6	15/01/2019 09:37	Pass		01-may-19	1	0
8	Issue7	15/01/2019 09:53	Pass		01-jun-19	1	0
9	Issue8	15/02/2019 09:51	Pass		01-dic-19	2	2
10	Issue9	15/03/2019 11:51	Pass
11	Issue10	15/03/2019 11:59	Pass
12	Issue11	15/04/2019 12:27	Pass
13	Issue12	15/05/2019 09:08	FAIL
14	Issue13	15/06/2019 09:13	FAIL
15	Issue14	15/12/2018 09:31	Pass
16	Issue15	15/12/2018 09:31	Pass
Hoja1

Cell Formulas
Range	Formula
F2:F9	F2	=SUMPRODUCT(($B$2:$B$16>=$E2)*($B$2:$B$16<=EOMONTH($E2,0)))
G2:G9	G2	=SUMPRODUCT(($B$2:$B$16>=$E2)*($B$2:$B$16<=EOMONTH($E2,0))*($C$2:$C$16="Pass"))

 

[Tetra201]

Two things to mention:

1) Generally, COUNTIFS works faster than SUMPRODUCT. In addition, entire columns can be used in COUNTIFS:

=COUNTIFS(B:B,">="&E2,B:B,"<"&EOMONTH(E2,0)+1)
=COUNTIFS(B:B,">="&E2,B:B,"<"&EOMONTH(E2,0)+1,C:C,"Pass")

2) The formulas suggested in Post # 2 will not count end-of-month dates with timestamps beyond 0:00:00, for example 31/01/2019 01:23:45

 

[DanteAmor]

@Tetra201, good catch.

Here the formula corrected with another approach:

Dante Amor
	A	B	C	D	E	F	G
1	Item	Date	Results		Month	Issues	Results (ONLY PASS)
2	Issue1	15/12/2019 17:49	Pass		dic-18	2	2
3	Issue2	15/12/2019 17:49	Pass		ene-19	5	4
4	Issue3	01/01/2019 00:00	Pass		feb-19	1	1
5	Issue4	01/01/2019 19:36	FAIL		mar-19	2	2
6	Issue5	15/01/2019 09:18	Pass		abr-19	1	1
7	Issue6	31/01/2019 09:37	Pass		may-19	1	0
8	Issue7	31/01/2019 23:59	Pass		jun-19	1	0
9	Issue8	15/02/2019 09:51	Pass		dic-19	2	2
10	Issue9	15/03/2019 11:51	Pass
11	Issue10	15/03/2019 11:59	Pass
12	Issue11	15/04/2019 12:27	Pass
13	Issue12	15/05/2019 09:08	FAIL
14	Issue13	15/06/2019 09:13	FAIL
15	Issue14	15/12/2018 09:31	Pass
16	Issue15	15/12/2018 09:31	Pass
Hoja1

Cell Formulas
Range	Formula
F2:F9	F2	=SUMPRODUCT(($B$2:$B$16>=$E2)*(INT($B$2:$B$16)<=INT(EOMONTH($E2,0))))
G2:G9	G2	=SUMPRODUCT(($B$2:$B$16>=$E2)*(INT($B$2:$B$16)<=INT(EOMONTH($E2,0)))*($C$2:$C$16="Pass"))

 

[waqasbutt]

Dear @DanteAmor,

Thank you, appreciated. I tried on sample data. Tomorrow I will try on actual data and let you know.
I guess SUMPRODUCT does not allow selection of complete column e.g fixing B colum =SUMPRODUCT(($B$:$B$>=$E2)*(INT($B$:$B$)<=INT(EOMONTH($E2,0))))

As when I tried fixing complete column it does not work.

Regards,
Waqas
Thank you very much. It work

 

[DanteAmor]

Apparently it doesn't work with the entire column.
Although sumproduct is captured as a regular formula, it is an array formula. Internally it makes the calculations as a data array.
Therefore it is not recommended to use this type of formulas with the entire column. It is always advisable to fix the data, if you have 2,000 records, for example, you can leave a slack up to 2,500.

If you need to use the entire column, there is the Tetra option.

 

[waqasbutt]

Dear @DanteAmor,

That's fine, I will fix the range.

First formula works: On my actual data, first formula works but second is not working for getting results.

Second formula does not properly works:
In my actual, I have 15000 rows but formula only works till 979 rows and strangely do not work after 979. If goes beyond 979 it shows #N/A.

I checked perhaps there is something wrong in 980 row but nothing odd there or there row in data.

=SUMPRODUCT(('Sheet2'!$AR$2:$AR$15000>=$D6)*(INT('Sheet2'!$AR$2:$AR$15000)<=INT(EOMONTH($D6;0)))*('Sheet2'!AL2:AL15000="YES"))

Issue	Reported Date	Results (it is calculated column as well)	Reported Date (=Table13[@[Reported Date]])
ISSUE10001​	14/01/2019 17:49:57	NO	Jan-19
ISSUE10001​	14/01/2019 18:08:26	YES	Jan-19
ISSUE10001​	14/01/2019 18:41:36	YES	Jan-19
ISSUE10001​	14/01/2019 19:36:39	NO	Jan-19
ISSUE10001​	15/01/2019 9:18:06	YES	Jan-19
ISSUE10001​	15/01/2019 9:37:55	YES	Jan-19
ISSUE10001​	15/01/2019 9:53:06	YES	Jan-19
ISSUE10001​	15/01/2019 9:51:50	YES	Jan-19
ISSUE10001​	15/01/2019 11:51:52	YES	Jan-19
ISSUE10001​	15/01/2019 11:59:55	YES	Jan-19
ISSUE10001​	15/01/2019 12:27:17	YES	Jan-19
ISSUE10001​	15/01/2019 9:08:04	YES	Jan-19
ISSUE10001​	15/01/2019 9:13:19	YES	Jan-19
ISSUE10001​	15/01/2019 9:28:02	YES	Jan-19
ISSUE10001​	15/01/2019 9:31:44	YES	Jan-19

 

[DanteAmor]

It works for me with 200,000 records.
I don't know if it's the excel version.
Did you try the Tetra formula?

 

[waqasbutt]

Hi DanteAmor,

Could be, that is why I am using now =COUNTIFS($D$2:$D$130000;C195;$I$2:$I$130000;"Yes").

Now I have another challenge.

I am calculating average of issues opened and when I tried to do averageifs function it does not work or says "You have enter too few arguments for this function".

Issue​	Date​	Results​	Reported Date​	Average Age​
A​	B​	C​	D​	E​
ISSUE10001	14/01/2019 17:49​	NO​	Jan-19​	-32​
ISSUE10001	14/01/2019 18:08​	YES​	Jan-19​	-32​
ISSUE10001	14/02/2019 18:41​	YES​	Feb-19​	43​
ISSUE10001	14/02/2019 19:36​	NO​	Feb-19​	-32​
ISSUE10001	15/02/2019 09:18​	YES​	Feb-19​	-32​
ISSUE10001	15/03/2019 09:37​	YES​	Mar-19​	-32​
ISSUE10001	15/03/2019 09:53​	YES​	Mar-19​	-32​
ISSUE10001	15/04/2019 09:51​	YES​	Apr-19​	-32​
ISSUE10001	15/04/2019 11:51​	YES​	Apr-19​	59​
ISSUE10001	15/05/2019 11:59​	YES​	May-19​	332​
ISSUE10001	15/05/2019 12:27​	YES​	May-19​	44​
ISSUE10001	15/05/2019 09:08​	YES​	May-19​	42​
ISSUE10001	15/06/2019 09:13​	YES​	Jun-19​	242​
ISSUE10001	15/06/2019 09:28​	YES​	Jun-19​	22​
ISSUE10001	15/06/2019 09:31​	YES​	Jun-19​	22​

Result required

Month​	Results​	Average Age​
A​	B​	E​
Jan-19​	1​	-32​
Feb-19​	2​	-7​
Mar-19​	2​	-32​
Apr-19​	2​	14​
May-19​	2​	139​
Jun-19​	3​	95​

Regards,
Waqas

 

[DanteAmor]

Now I have another challenge.

I recommend you create a new thread for each topic.