Count the number of Excel records that fall between two dates

Using Excel's COUNTIFS() function, you can quickly count records that fall between two dates.

Many records include a date stamp of some sort. Usually the date marks an event or the input date. Either way, counting the number of records that fall between two dates is easy using the COUNTIFS() function (which isn't available in Excel 2003 or earlier). To illustrate, the following sheet has a number of date-related records.  The formula in cell B3


returns the number of dates (records) in A5:A12 that fall between the  dates in B1 and B2.

The COUNTIFS() function uses the following syntax to specify multiple criteria to determine which values in a range to count:

COUNTIFS(countrange1, criteria1, [countrange2, criteria2]...)

In this example, the count range is the same for each criteria set, the dates in column A. The first criteria set counts all the dates in A5:A12 that are equal to or fall after the date value in B1. The second criteria set counts dates that are equal to or occur before the date value in B2.

The only stipulation with this particular setup is that the value in B1 must be less than the value in B2. If you enter them backward, the function will return 0, instead of an error. To apply a conditional format to alert your users, do the following:

  1. Select B2.
  2. Click the Home tab and then click Conditional Formatting in the Styles group.
  3. Choose New Rule.
  4. Choose the Format Only Cells That Contain option.
  5. Retain Cell Value in the first dropdown-that's what you want.
  6. Choose Less Than from the second dropdown.
  7. Enter =$B$1 in the final control.
  8. Click Format.
  9. Click the Fill tab, choose a background color, and click OK.
  10. Click OK again.

The format won't stop a user from making the mistake, but will alert them that the value they've chosen isn't appropriate.


Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.


I need some help with Excel.
I have a date column “A”
I have a number column “H”
I want to count all the records in column H that are > 0 that are within a specific date range in column A.
I get close but cant seem to get the right formula down.
Tried using Countifs and SumProduct



Please I Don't Find  1 Excel Date Column & Other Column Count Example   

 This 1 Column                            2 Column is      

 Row 1   10-July-14                Row 1        AAN

 Row 2  01-Aug-14                 Row 2        ANR

Row  3   12-Sep-14                Row 3        AAN

I Count Per Month AAN or ANR How Many Count

 I Tray To  1 Month But Not Found

  Please Sold my Problem & send My


hi please find the below attachment, here i have given vertical plat and des but i need horzontally how many u beongs plant and how many p w.r.t plant.. please give the solution

Plant    Des 

hpr       u

hpr      p

fet       u

 fet       p

 lcp      u

 lcp      p 

 hpr     u

 hpr     u 

Above data i have but i need like below


       u     p





This is an entirely new functionality. Don't get confused by the example cited, which uses the same range for range criteria 1 and range criteria 2. This formula works as a "countif a is true in range 1, AND b is true in range 2, AND c is true in range 3... (in up to 127 ranges of the same size)".

Kent Lion
Kent Lion

COUNTIFS and SUMIFS are nice to have, but will return a #NAME? error in versions of Excel earlier than 2007. I (and others who don't want the decreased efficiency of the ribbon system) won't buy 2007+ for something that can be done relatively easily in other ways. Also, it has always been possible in Excel to destroy protected formulas on protected sheets that refer directly to unprotected cells (don't know if it has been fixed in 2010, it hasn't in 2007), as well as unprotected cell formats. A thus destroyed formula (e.g., by cutting and pasting B1 to B2, which would be encouraged by flagging an error), will show up as a #REF! error; however, a conditional format won't tell the user what's wrong and the user won't know if the format has been destroyed. For these reasons, it's not a good idea to count on conditional formatting of input cells to flag "bad" input. A computed error message beside the input cell would do the job (e.g., =IF(B1>B2,Ending date is earlier than beginning date!,), if the job were necessary. However, the order in which two limits are given does not change what is between those limits, so requiring they be ordered creates a problem where there should be none. There is no such thing as fool-proof. If a user has to correct an error, it adds another chance to screw up and is less efficient. The user can do that without Excel. Whatever can be done more reliably by Excel, should be. In Excel 2007 or higher only: =COUNTIFS(A5:A12,">="&MIN(B1,B2),A5:A12,"LE"&MAX(B1,B2)) should work (I don't have 2007 or 2010 here to test it with). In any version of Excel that includes COUNTIF: =COUNTIF(A5:A12,"LE"&MAX(B1,B2))-COUNTIF(A5:A12,"LT"&MIN(B1,B2)) which works in 2003. If you want the labels to reflect this change, instead of "Beginning Date" and "Ending Date" you might use "Between Date:" and "...and Date:", right justified in their cells.

Kent Lion
Kent Lion

@jbenton; you need to check your above entries. This form does not seem to be capable of displaying the less than symbol, and its use can cause things after it to be ignored, certainly up to and including the next greater than symbol. Somehow that doesn't seem appropriate in a form where we're trying to comment on Excel formulas... With that in mind, in my following comment, "LT" means "less than", and "LE" means "less than or equal to".


you can do this with an array formula: =SUM((A5:A12>=B1)*(A5:A12

Editor's Picks