Excel - COUNTIF Problem
"=COUNT(AND(Sheet1!L2:L96<=DATE(2009,3,31)),(Sheet1!O2:O96>=DATE(2009,4,1)))
I am trying to look at Column L and if the date is earlier than 2009,4,1 and the date in that row in Column O is later than 2009,4,1 THEN count that entry. I have tried all sorts of permutations and the above is a correct formula but it does not return the correct value.
A COUNTIF function can have only one test so revert to either SUMPRODUCT or COUNT Array, SUMPRODUCT generally a little easier to setup and adapt long-term:
=SUMPRODUCT(--(Sheet1!L2:L96<=DATE(2009,3,31)),--(Sheet1!O2:O96>=DATE(2009,4,1)))
(those running XL2007 should opt for use of the new COUNTIFS fn which as the name implies caters for multiple tests)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks