Excel and Office RAQ

Getting COUNTIF to work with other functions in Excel

   

Tactical solutions

Research projects

Microsoft Office

Multiple choice guessing

Services

Knowledge measurement audit

Item bank services

Item design course

Products

WaterMarker

iOTA

STASiS

ACiS

CaSelector

CaSCADE

Technical articles

Excel and Office RAQ

Free Excel Add-in

Free PowerPoint Add-in

Contact

Home page

Q: I'm trying to do a check on the number of dates in a row which are less than (or equal to) today's date. This figure would then be used in a calculation. I have tried various options, but without success. My target is to have

        =COUNTIF(A2:O2,"<=Today")

But I'm getting a 0 result, even though there should be several. I've checked formatting for all dates is correct, and that the Countif is formatted as a number. I must be missing something obvious, but can't see it.

A: The obvious thing you're missing is far from obvious!

Firstly, for some reason, Excel treats TODAY as a function rather than a variable, so it always needs to be followed by an empty set of brackets, thus:

        TODAY()

My starting point for the formula was this:

        =COUNTIF(A2:O2,"<=TODAY()")

Just like you, I was expecting some hits, but got a zero. I started thinking about the effect of putting a formula inside a string, and realised that it wouldn't work that way. So I built a way of resolving the function outside the string and then tagging the result onto it, like this:

        =COUNTIF(A2:O2,"<="&TODAY())

This version actually works like you want.

 

 

Back to other RAQ topics