Solved

CountIf? Excel 2007 - How to count the number of rows containing a date that is in the past (before today)

Posted on 2010-08-28
10
242 Views
Last Modified: 2012-05-10
I have tried a variety of formlas based on countif but no luck.

CountIf("D1:D500", "<" & TODAY()) will not work in my macro. It doesn't like the colon between D1 and D500. What am I doin wrong?

Thanks,
0
Comment
Question by:swjtx99
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 total points
ID: 33550882
The formula in Excel would be:=COUNTIF(D1:D500,"<"&TODAY())In a macro, to assign the formula to a cell:Range("a1").Formula = "=COUNTIF(D1:D500,""<""&TODAY())"To a variable:MyVariable = Application.CountIf(Range("D1:D500"),"<" & Date)
0
 
LVL 4

Expert Comment

by:Jamie_Wilson
ID: 33550886
Why do you have " marks round D1:D500 and <?
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33550906
This is probably easiest to use in VBA

MsgBox [countif(D1:D500, "<"&Today())]

Patrick,

I think you left out "WorksheetFunction".  However, I tried this and it does not give the correct answer...

MyVariable = Application.WorksheetFunction.CountIf(Range("D1:D500"),"<" & Date)
0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 250 total points
ID: 33550910
But this works

Application.WorksheetFunction.CountIf(Range("A1:A15"), "<" & (Date * 1))
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33551119
cyberkiwi,You might be surprised to know that WorksheetFunction is not actually necessary :)Patrick
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:swjtx99
ID: 33551136
Thanks for the replies. This isn't giving me any errors in VBA, however there are 55 rows with a date in column E that are in the past and this says there are only 14. The goal is to have excel give me a total count in the left header as well as a count of rows "past due". Everything works except the count of rows with a date in column E that is prior to the current date.

My entire line of code is:

.LeftHeader = "&""Arial,Bold""&14Total WIP = " & Cells.SpecialCells(xlCellTypeLastCell).Row - 1 & "&""Arial,Bold""&14Total Past Due = " & Application.WorksheetFunction.CountIf(Range("E1:E15"), "<" & (Date * 1))

The entire section is:

ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = "&""Arial,Bold""&14Total WIP = " & Cells.SpecialCells(xlCellTypeLastCell).Row - 1 & "&""Arial,Bold""&14Total Past Due = " & Application.WorksheetFunction.CountIf(Range("E1:E15"), "<" & (Date * 1))
        .CenterHeader = "&""Arial,Bold""&14Cell C WIP Goal </= 140"
        .RightHeader = Format(Date, "mmmm dd, yyyy")
        .LeftFooter = ""
        .CenterFooter = "Page &P of &N"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 20
        .PrintErrors = xlPrintErrorsDisplayed
    End With
0
 

Author Comment

by:swjtx99
ID: 33551141
.......oh boy how stupid I am......I should probably change the search criteria to more than 15 rows huh?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33551158
Only if you want the right answer :)
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33551183
Patrick,

> You might be surprised to know that WorksheetFunction is not actually necessary :)

My surprise of the day!
The Date *1 is necessary though, to make the criteria something like ">40419" (internal date number) rather than a string comparison.

1-all :)
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33551223
>>The Date *1 is necessary though, to make the criteria something like ">40419" (internal date number) rather than a
>>string comparison.

Touché :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
User Form VBA Data + Print Sections 39 114
In search of x17-22375.exe 2 15
Filling Blank Cells 14 21
Macro Lookup 33 0
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now