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
276 Views
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
Question by:swjtx99
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3
• 2
• +1

LVL 93

Accepted Solution

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

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

LVL 58

Expert Comment

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

cyberkiwi earned 250 total points
ID: 33550910
But this works

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

LVL 93

Expert Comment

ID: 33551119
cyberkiwi,You might be surprised to know that WorksheetFunction is not actually necessary :)Patrick
0

Author Comment

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)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintGridlines = False
.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

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

LVL 93

Expert Comment

ID: 33551158
Only if you want the right answer :)
0

LVL 58

Expert Comment

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 93

Expert Comment

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

0

## Featured Post

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst youâ€™ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to dâ€¦
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are â€¦
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usâ€¦
###### Suggested Courses
Course of the Month4 days, 5 hours left to enroll