Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

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,
0
swjtx99
Asked:
swjtx99
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
Patrick MatthewsCommented:
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
 
Jamie_WilsonCommented:
Why do you have " marks round D1:D500 and <?
0
 
cyberkiwiCommented:
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
cyberkiwiCommented:
But this works

Application.WorksheetFunction.CountIf(Range("A1:A15"), "<" & (Date * 1))
0
 
Patrick MatthewsCommented:
cyberkiwi,You might be surprised to know that WorksheetFunction is not actually necessary :)Patrick
0
 
swjtx99Author Commented:
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
 
swjtx99Author Commented:
.......oh boy how stupid I am......I should probably change the search criteria to more than 15 rows huh?
0
 
Patrick MatthewsCommented:
Only if you want the right answer :)
0
 
cyberkiwiCommented:
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
 
Patrick MatthewsCommented:
>>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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now