Hi,
I'm attempting to use VBA (has to be vba) to count the number of rows of data that exist when a date in column O exists between two specific dates.
I have the people selecting the dates they need. I have no idea how the countif (or countifs) statement will work for
There are so many examples on the forums, but i can't see to get them to work what what I need
Please Login or Register to view this content.
Any help is greatly appreciated!
Do you have a sample that you can upload?
To Attach a File:
1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Thanks for the quick reply
Unfortunately I can't attach a sampleas I'd probably lose my job as it is private information of our clients.
Column O is the date field in format mm/dd/yyyy. This is part of a larger macro that will calculate various types of data measurements. I'm able to pull the information such as if another field equals a word using the Application.WorksheetFunction.CountIf function... but I'm not sure how to make it count a row if the date within column O is between the specified dates inputted by the startdate1 and startdate2. The startdates are working as I have them being posted to a cell (for troubleshooting purposes).
You do not have to attach the real file. A sample file with dummy data will do as long as it matches the original file with respect to its layout.
Try
Please Login or Register to view this content.
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
Thank you.. that did exactly what was required!
sorry- i guess i spoke too soon. I should have mentioned I'm using excel 2003. When i tested your line of code, i tested it on my home computer with the sample data I was preparing to post, which is running Excel 2010 and it worked great; but, when i went to put the line in my 2003 version (which is what our company uses) i now get this error:
Run-time error '438': Object doesn't support this property or method.
I've added the sample data with the macro.. it works under 2010.. but not 2003 which is what i need.
Last edited by Rilly; 02-23-2013 at 01:05 PM. Reason: fixing grammar
If I remember correctly 2003 does not have COUNTIFS
The formula you'ld want in the cell is =COUNTIF(O:O, "<=" & startDate2) - COUNTIF(O:O, "<" & startDate1)
The VBA would be
Please Login or Register to view this content.
from what you pasted, i've used it as this (as I think the code was missing the application, and the sheets defined, and had < instead of >..
but - it is not coming out with the same values as if I manually sort. How can I troubleshoot to see which records it is counting? Manually filtering i get 110, but using the formula i'm getting 183 (of 300 records)Please Login or Register to view this content.
Think about the inequlities again.
The OP is asking for the
( count of dates that are both (greater than or equal to startDate1) and (less than or equal to startDate2) )
That is the same as
( (count of dates that are less than or equal to startDate2) minus (count of dates that are less than startDate1) )
Hi Rilly
Can you check if this version:
gives you the same result as manually sorting?Please Login or Register to view this content.
This version is based on CountIf formula example I've found in MS Excel help.
Last edited by Sbarro79; 02-23-2013 at 08:36 PM. Reason: missing letter
Hi Sbarro79,
Yes - that is giving me the correct # no, matching what I get when running manually. I try to use the help, but I can never figure out the syntax correctly!
mikerickson - yes, that makes sense now on what you were doing with both less than operators - I didn't think of calculating it that way. With Sbarro70's formula, its almost the same, but in reverse.
Thank you everyone for the help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks