Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
282 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
[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
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1000 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 58

Assisted Solution

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

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

Expert Comment

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

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 93

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 93

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

670 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