SUMPRODUCT involving comparison to today's date

[Human_doing]

Hi all,

Can anyone please help with this code

    Dim LRTotal As Long
    With ActiveSheet
 
 
    LRTotal = .Range("B" & .Rows.Count).End(xlUp).Row
    .Range("F2").Formula = "=COUNTIF(B2:B" & LRTotal & ",""*"")"
    .Range("F3").Formula = "=COUNTIF(B2:B" & LRTotal & ",""COMPLETED"")"
    .Range("F4").Formula = "=COUNTIF(B2:B" & LRTotal & ",""ABANDONED"")"
    .Range("F5").Formula = "=SUMPRODUCT((TODAY()-C2:C" & LRTotal & "+0)*(B2:B" & LRTotal & ",""APPROVED"")"
    End With

The issue is with the output in to cell F5 which is supposed to total the number of rows where column B = 'APPROVED' and column C = > today's date, however I get run time error 1004 'application defined or object defined error' when I try to run this? All the other formulae calculate correclty,

Any help much appreciated,

Thanks

 

[Trevor G]

In VBA Today is expressed as Date, that might clear this up

 

[Human_doing]

Hi thanks for getting back to me so quickly but I'm not sure how to amend the formula to make this work. The objective is to produce a count in the cell of all records where date in column C has already passed and status in column B is 'APPROVED', can anyone please help?

Thanks

 

[Trevor G]

Which version of Excel are you using as CountIFS works in 2007 or 2010

Sheet5

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 68px"><COL style="WIDTH: 83px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 126px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Count of Approved</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Approved</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">01/08/2011</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>approved</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">02/08/2011</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Not yet</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">03/08/2011</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Not yet</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">04/08/2011</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Not yet</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">05/08/2011</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Approved</TD><TD style="TEXT-ALIGN: right">09/08/2011</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>Approved</TD><TD style="TEXT-ALIGN: right">09/08/2011</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>Approved</TD><TD style="TEXT-ALIGN: right">09/08/2011</TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>F2</TD><TD>=COUNTIFS(B2:B9,"Approved",List,"<"&TODAY())</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[Human_doing]

Hi, thanks so much for your help on this but I'm using Excel 2003.

If it helps I've added code that puts today's date in cell H1 and tried to base the formula on that but still not getting the correct result, could you please assist?

Thanks

 Dim LRTotal As Long
    With ActiveSheet
 
 
    LRTotal = .Range("B" & .Rows.Count).End(xlUp).Row
    .Range("H1").Formula = "=NOW()"
    .Range("F2").Formula = "=COUNTIF(B2:B" & LRTotal & ",""*"")"
    .Range("F3").Formula = "=COUNTIF(B2:B" & LRTotal & ",""COMPLETED"")"
    .Range("F4").Formula = "=COUNTIF(B2:B" & LRTotal & ",""ABANDONED"")"
    .Range("F5").Formula = "=SUMPRODUCT((B2:B" & LR & "=""COMPLETED"")*(C2:C" & LR & ">Sheet1!H1))"
    End With