Count If function

[chatnet26]

Dear Guru,

We have one excel file for monitoring of action items generated by the management after the study. As since there were around 3000+ rows has been generated since in the beginning of 1990's till to-date. So I was thinking of instead of getting the result through filter manually, I want to create a formula that will count of how many has been closed this year and this month out of the total numbers of action items.

Is it possible to use the COUNTIF function formula to count the number of items in column A, and date of column B, and closed in column C.

In below, we can see that there were 4 items under Revalidation has been closed this month and the total number of closed this year is 6.

TYPE	MTD Closing Date	Status
Project	2-Jan-13	Closed
Project	2-Jan-13	Closed
Project	5-Jan-13	Closed
Project	19-Feb-13	Closed
Project	16-Feb-13	Closed
Revalidation	22-Feb-14	Closed
Revalidation	2-Feb-14	Closed
Revalidation	5-Mar-14	Closed
Revalidation	8-Mar-14	Closed
Revalidation	10-Mar-14	Closed
Revalidation	15-Mar-14	Closed

<colgroup><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col></colgroup><tbody></tbody>

Appreciated you valuable response. Thanks

Best Regards,

 

[cornflakegirl]

I would think that the most efficient way to do this would be to create a pivot table.

 

[chatnet26]

Hi,

Many thanks to your quick response, however if u can suggest the way I should introduce as am not really an expert of excel specially you told pivot table. So if the countif function can perform that would be better.

Best regards,

 

[gazz]

Hopefully I understood this right. You can use SUMPRODUCT to do this.

If you data starts at cell A1, make table at E1 as follows

	E	F	G	H
1		2013	2014	March
2	Project	5	0	0
3	Revalidation	0	6	4

<tbody>
</tbody>

formula in F2 is =SUMPRODUCT(($A$2:$A$12=$E2)*(YEAR($B$2:$B$12)=F$1)*($C$2:$C$12="Closed")). Copy this to include F2:G3

formula in H2 =SUMPRODUCT(($A$2:$A$12=$E2)*(TEXT($B$2:$B$12,"mmmm")=H$1)*($C$2:$C$12="Closed")), copy to H3

This should get you going I think. You can extend it as required, for example the month in H1 could be a drop down pick list.

Hope this helps.

 

[cornflakegirl]

It can be done with SUMPRODUCT as above (or SUMIFS if you're using 2010). The advantage of a pivot table is that it doesn't require you to maintain a list of all possible categories. It depends on the complexity of your data as to which will suit best.

If you're new to pivot tables then Debra Dalgleish has a really helpful website.

 

[chatnet26]

Hi gazz,

Many thanks to your valuable response. It is definitely met my requirements. Appreciated so much, again thank you.

God Bless.

Best Regards,

 

[chatnet26]

Hi Emma,

I will go through the link you have provided and thank you with your kind assistance and input.

God Bless.

Best Regards,

 

[gazz]

You're very welcome. I'm happy I could help.

Regards

 

[chatnet26]

Hi gazz,

I found a bit problem in the formula of counting the current month because it will read/count which has been closed even in the previous years that falls to the month of March. It gives me a total of 581.

I need your help please to figure it out. Thanks

Best Regards,

 

[Aladin Akyurek]

TYPE	MTD Closing Date	Status		3/8/2014	This Month	This Year
Project	2-Jan-13	Closed		Project	0	0
Project	2-Jan-13	Closed		Revalidation	4	6
Project	5-Jan-13	Closed
Project	19-Feb-13	Closed
Project	16-Feb-13	Closed
Revalidation	22-Feb-14	Closed
Revalidation	2-Feb-14	Closed
Revalidation	5-Mar-14	Closed
Revalidation	8-Mar-14	Closed
Revalidation	10-Mar-14	Closed
Revalidation	15-Mar-14	Closed

<tbody>
</tbody>

E2:

=TODAY()

F2, copied down:

=COUNTIFS(
  $A$2:$A$12,E2,
  $B$2:$B$12,">="&$E$1-DAY($E$1)+1,
  $B$2:$B$12,"<="&EOMONTH($E$1,0),
  $C$2:$C$12,"closed")

G2, copied down:

=COUNTIFS(
  $A$2:$A$12,E2,
  $B$2:$B$12,">="&"1-Jan-"&YEAR($E$1),
  $B$2:$B$12,"<="&"31-Dec-"&YEAR($E$1),
  $C$2:$C$12,"closed")