swjtx99
asked on
CountIf? Excel 2007 - How to count the number of rows containing a date that is in the past (before today)
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,
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,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Why do you have " marks round D1:D500 and <?
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.WorksheetFunct ion.CountI f(Range("D 1:D500")," <" & Date)
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.WorksheetFunct
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
cyberkiwi,You might be surprised to know that WorksheetFunction is not actually necessary :)Patrick
ASKER
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(xlCellT ypeLastCel l).Row - 1 & "&""Arial,Bold""&14Total Past Due = " & Application.WorksheetFunct ion.CountI f(Range("E 1:E15"), "<" & (Date * 1))
The entire section is:
ActiveSheet.PageSetup.Prin tArea = ""
With ActiveSheet.PageSetup
.LeftHeader = "&""Arial,Bold""&14Total WIP = " & Cells.SpecialCells(xlCellT ypeLastCel l).Row - 1 & "&""Arial,Bold""&14Total Past Due = " & Application.WorksheetFunct ion.CountI f(Range("E 1: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
My entire line of code is:
.LeftHeader = "&""Arial,Bold""&14Total WIP = " & Cells.SpecialCells(xlCellT
The entire section is:
ActiveSheet.PageSetup.Prin
With ActiveSheet.PageSetup
.LeftHeader = "&""Arial,Bold""&14Total WIP = " & Cells.SpecialCells(xlCellT
.CenterHeader = "&""Arial,Bold""&14Cell C WIP Goal </= 140"
.RightHeader = Format(Date, "mmmm dd, yyyy")
.LeftFooter = ""
.CenterFooter = "Page &P of &N"
.RightFooter = ""
.LeftMargin = Application.InchesToPoints
.RightMargin = Application.InchesToPoints
.TopMargin = Application.InchesToPoints
.BottomMargin = Application.InchesToPoints
.HeaderMargin = Application.InchesToPoints
.FooterMargin = Application.InchesToPoints
.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
ASKER
.......oh boy how stupid I am......I should probably change the search criteria to more than 15 rows huh?
Only if you want the right answer :)
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 :)
> 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 :)
>>The Date *1 is necessary though, to make the criteria something like ">40419" (internal date number) rather than a
>>string comparison.
Touché :)
>>string comparison.
Touché :)