Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I need to simplify a CountIF formula.

Instead of having a date range I just need my COUNTIF to count cells cointaining dates disregarding date range period.

I have tried for example simplifying it by just =Countif(E84:H91),"Dates". I have also tried =CountIf(E84:H91),"dd.mm.yy" with no luck.

This is probably going to be facepalm easy but any formula suggestion appreciated, or should I be using a different Count Function all together? I have heard/seen the Dcount function but never used. Could that be a better option?

share|improve this question

migrated from stackoverflow.com Apr 23 '13 at 22:47

This question came from our site for professional and enthusiast programmers.

    
Any non-negative number can be interpreted as a date in Excel. I am not sure there is a way to do this without using a UDF. Can you safely make any assumptions about the data, e.g., "All cells containing a / contain Date values"? If so, I have an idea that doesn't require any VBA. –  David Zemens Apr 23 '13 at 2:40

3 Answers 3

Yeah lots of numbers in the range count won't work. It's a master data entry page which will need to be duplicated as new "clients" are made and they all need the ability to be saved independently and to all feed to another workbook that has the collated stats. Im useing dropbox for networking and feeding links so I have that part figured out its just this annoying need to calculate how many sales per master which i wanted to do by reading the dates the dates are selected from a drop down box I'm wondering if that's why my numbers are coming out funny but I've made time sheets and many other data focussed workbooks useing drop down boxes and the hidden list never seemed to have a impact before-everything I try with countif is being accepted but generating weird numbers that above explanations do not match.

I have found a word that occurred only once per transaction - definetly not what I wanted to do but I may just use the countif formula on that particular word instead to count transactions - just don't like doing it that way but oh well. Appreciate all the feedback and your suggestions all worked on a dummy draft I fiddled with at home it must be hidden data interfering somehow. It's quite a large spreadsheet I have so many hidden columns I wouldn't be surprised-too much trouble to go through and trouble shoot i should of thought the layout through better. So a bandaid fix it is :D

share|improve this answer

You can use CountIf and provide a criterion such as

=COUNTIF(F2:F74;=>"1/1/2001")

Where "1/1/2001" is the earliest date that your range will be. You can't use it without VBA if you need it from say 1/1/2001 until 1/1/2015 but perhaps there is some logic you can use to interpret it as stated.

My recommendation would be to decide on what data will NOT appear and then build your formula around cancelling out what may appear ex - 01/01/1901 will be something like 367 in value, 01/01/2100 will be something like 73051. If you know something like 01/01/1901 isn't possible you could use =>"1/2/1901" or => value(398) in your formula

share|improve this answer

What other values might be in the range, can there be numbers which aren't dates? If not then use COUNT function, e.g.

=COUNT(E84:H91)

Dates in excel are simply formatted numbers - COUNT function counts all numbers

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.