Thursday, January 16, 2014

Excel - Countifs Using Equal To or Greater Than with a Date Field

There's a wrinkle in the countifs() formula for using a date field rather than an explicitly declared date in the formula.

Here's an example:
=COUNTIFS($P$15:$P$2050,"91+",$D$15:$D$2050,$A$1,$Q$15:$Q$2050,">=1/10/2013")

I wanted to replace the "1/10/2013" with a date field in A3.

My first attempt was this:
=COUNTIFS($P$15:$P$2050,"91+",$D$15:$D$2050,$A$1,$Q$15:$Q$2050,">=$A$3")
Which doesn't work.  My next try was this:

=COUNTIFS($P$15:$P$2050,"91+",$D$15:$D$2050,$A$1,$Q$15:$Q$2050,">="$A$3)
Again, that didn't work.


Now here's the correct way to use a date field in Countifs():

=COUNTIFS($P$15:$P$2050,"91+",$D$15:$D$2050,$A$1,$Q$15:$Q$2050,">="&$A$3)

See that little ampersand between the greater than and equal section and the field?  That's the trick.


You're welcome.

No comments:

Post a Comment