# Discussion on: Count the number of Excel records that fall between two dates

11

### Join the conversation!

View:
Show:
###### pre Excel 2007...
jbenton@... Updated - 27th Mar 2012
you can do this with an array formula:
=SUM((A5:A12>=B1)*(A5:A12=B2))
NB don't forget to use ctrl-shift-enter instead of enter to complete the entry
(this version looks shorter and easier to follow to me anyway)
###### or
jbenton@... 27th Mar 2012
=SUMPRODUCT((A5:A12>=B1)*(A5:A12=B2))
and press enter as normal
###### I like both of these
N / A 3rd Apr 2012
Even if they are less easily readable than the countifs (to those not fluent in Excelspeak).
###### In another situation
Mark.Mathews 5th Apr 2012
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.
###### Learning Curve
Mark.Mathews 5th Apr 2012
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.
###### even neater
jbenton@... 8th Apr 2012
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
###### Yes, TRIM can be a big help
N / A 10th Apr 2012
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.
###### TR, you have an error in your comment form!
Kent Lion Updated - 27th Mar 2012
@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".
###### Why conditional formatting?
Kent Lion Updated - 27th Mar 2012
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.
###### Don't miss the point
Mark.Mathews 3rd Apr 2012
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)".
###### what's the point?
jbenton@... 4th Apr 2012
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?
Keyboard Shortcuts:
Prev
Next
Toggle
###### Join the conversation
Formatting +
BB Codes - Note: HTML is not supported in forums
• [b] Bold [/b]
• [i] Italic [/i]
• [u] Underline [/u]
• [s] Strikethrough [/s]
• [q] "Quote" [/q]
• [ol][*] 1. Ordered List [/ol]
• [ul][*] · Unordered List [/ul]
• [pre] Preformat [/pre]
• [quote] "Blockquote" [/quote]

Join the TechRepublic Community and join the conversation! Signing-up is free and quick, Do it now, we want to hear your opinion.