Count of rows for a specific date

[phaniakella]

Dear Experts

I am trying to get the count of number of rows for a specific date say (5/2/2011). I am not able to get this using countifs function.

I tried

countifs(A1:A10,"=5/2/2011")
countifs(A1:A10,"5/2/2011")
countifs(A1:A10,"="&5/2/2011)

Nothing worked. Please need your help. Thank You.


1 ABC Assigned 5/2/2011
2 ABC Assigned 5/13/2011
3 ABC Closed 5/13/2011
4 ABC Assigned 5/2/2011
5 ABC Assigned 5/13/2011
6 ABC Assigned 5/2/2011
7 ABC Closed 5/13/2011
8 ABC Closed 5/2/2011
9 ABC Closed 5/13/2011
10 ABC Closed 5/2/2011

 

[crook_101]

Hi,


Ensuure your dates are fomatted as cells......

Try:

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">05/02/2011</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">05/02/2011</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">05/02/2011</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">06/02/2011</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">07/02/2011</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">08/02/2011</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">09/02/2011</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">10/02/2011</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">3</TD></TR></TBODY></TABLE>

Sheet1​

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>C11</TH><TD style="TEXT-ALIGN: left">=COUNTIF(C3:C10,"05/02/2011")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Cheers,
Ian

 

[T. Valko]

Dear Experts

I am trying to get the count of number of rows for a specific date say (5/2/2011). I am not able to get this using countifs function.

I tried

countifs(A1:A10,"=5/2/2011")
countifs(A1:A10,"5/2/2011")
countifs(A1:A10,"="&5/2/2011)

Nothing worked. Please need your help. Thank You.


1 ABC Assigned 5/2/2011
2 ABC Assigned 5/13/2011
3 ABC Closed 5/13/2011
4 ABC Assigned 5/2/2011
5 ABC Assigned 5/13/2011
6 ABC Assigned 5/2/2011
7 ABC Closed 5/13/2011
8 ABC Closed 5/2/2011
9 ABC Closed 5/13/2011
10 ABC Closed 5/2/2011

Use a cell to hold the date to be counted:

Book1
	A	B	C
1	ABC	Assigned	5/2/2011
2	ABC	Assigned	5/13/2011
3	ABC	Closed	5/13/2011
4	ABC	Assigned	5/2/2011
5	ABC	Assigned	5/13/2011
6	ABC	Assigned	5/2/2011
7	ABC	Closed	5/13/2011
8	ABC	Closed	5/2/2011
9	ABC	Closed	5/13/2011
10	ABC	Closed	5/2/2011
11	_	_	_
12	5/2/2011	5	_
Sheet1

Formula entered in B12:

=COUNTIF(C1:C10,A12)

 

[phaniakella]

Hi Ian/Valko

Thanks for the solution. I was referring to a wrong date formats. Dates in my excel sheet were in MM/DD/YYYY HH:MM:SS format. I have changed it to Text using the Text function in excel and then applied countifs. It worked.

=TEXT(DATE FIELD,"mm/dd/yyyy").

Thanks for your help...

Cheers,
Phani Akella

 

[T. Valko]

Hi Ian/Valko

Thanks for the solution. I was referring to a wrong date formats. Dates in my excel sheet were in MM/DD/YYYY HH:MM:SS format. I have changed it to Text using the Text function in excel and then applied countifs. It worked.

=TEXT(DATE FIELD,"mm/dd/yyyy").

Thanks for your help...

Cheers,
Phani Akella

Good deal. Thanks for the feedback!

Another way to do it without having to change the date/time entries...

A12 = 5/2/2011

=SUMPRODUCT(--(INT(C1:C10)=A12))