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
231 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

757 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

20 Experts available now in Live!

Get 1:1 Help Now