Software

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

=COUNTIFS(A5:A12,">="&B1,A5:A12,"<="&B2)

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.

About

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.

13 comments
Rafiqulislam
Rafiqulislam

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 Email:Rafiqul4482@gmail.com

rajshe
rajshe

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

Hpr

fet

lcp

Mark.Mathews
Mark.Mathews

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".

jbenton
jbenton

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

jbenton
jbenton

The other solutions given will also work for conditions on different-but-same-size ranges (but without limit - not that I believe anyone would reach it) The 'new functionality' provides a less geeky way of doing it, though using the array (or Boolean) method does allow you to construct more convoluted conditions (using '*' for 'AND', '+' for 'OR') Similar techniques allow you to avoid tortuous 'nested IF' statements, but that's different topic for another time, eh Susan?

Mark.Mathews
Mark.Mathews

Ok, try this: Name, Cook, Baker Jim, Yes, No John, No, Yes Julie, Yes, Yes Joann, No, Yes Now write an array formula to show how many people are both Cook and Baker, as simple as: =COUNTIFS(B2:B5, "Yes", C2:C5, "Yes") Probably you can do it, but I'm not familiar with the array formula, and I wonder if it will be this simple.

jbenton
jbenton

=SUMPRODUCT((A5:A12>=B1)*(A5:A12

Mark.Mathews
Mark.Mathews

Seems I answered my own question when I tried the array formula you suggested, with the Ctrl+Shift+Enter. Doh! Not sure why MS decided to make a new function to do the same thing as another function. Maybe the thinking is "Different is Better". Go figure.

RU7
RU7

Even if they are less easily readable than the countifs (to those not fluent in Excelspeak).

RU7
RU7

Users often include extraneous spaces in their input. It is even worse in Word. The appllication may have some part in it but I rarely look at a Word document without finding paragraphs that end in a period-space-paragraph mark. Although that is less ubiquitous in Excel, TRIM can preempt many headaches associated with extraneous white space.

jbenton
jbenton

if you'd used the logicals TRUE/FALSE instead of yes/no then the array counter could simply be =SUM(B2:B5*C2:C5) NB entered as an array with ctrl-sh-enter using yes/no the array formula needed is =SUM((B2:B5="Yes")*(C2:C5="Yes")) or =SUM((TRIM(B2:B5)="Yes")*(TRIM(C2:C5)="Yes")) if your entries may contain aberrant spaces

Editor's Picks