Date issue with SUMPRODUCT

[Human_doing]

Hi all,

I can get sumproduct to work in this case:

=SUMPRODUCT(--(B2:B349="COMPLETED"),--(C2:C349="CAT"))

Where one entry is returned, which is correct (I tried this for test purposes) but when it comes to using dates it doesn't seem to work - if I put in

=SUMPRODUCT(--(B2:B349="COMPLETED"),--(C2:C349="01/01/2011"))

When there should be one result, it comes back as 0 results. Can anyone amend the formula or explain why there seems to be a difference when it comes to using this for dates?

Thanks

 

[VoG]

Try

=SUMPRODUCT(--(B2:B349="COMPLETED"),--(C2:C349="01/01/2011"+0))

 

[Human_doing]

Thanks that's really helpful, much appreciated,

Cheers

 

[anglais428]

Untested:
change to
=SUMPRODUCT(--(B2:B349="COMPLETED"),--(C2:C349=DATE(2011,1,1))

 

[Human_doing]

Thanks, can you please help with altering the formula so that in vba all used rows are selected and the criteria are 1 = 'COMPLETED' in column B and 2. date in column C has already passed?

I've tried =SUMPRODUCT(--(B2:B" & LRTotal & "=""COMPLETED""),--(C2:C" & LRTotal & "=""NOW()""<1))" but no success, any help much appreciated,

Thanks

 

[Jonmo1]

Thanks, can you please help with altering the formula so that in vba all used rows are selected and the criteria are 1 = 'COMPLETED' in column B and 2. date in column C has already passed?

I've tried =SUMPRODUCT(--(B2:B" & LRTotal & "=""COMPLETED""),--(C2:C" & LRTotal & "=""NOW()""<1))" but no success, any help much appreciated,

Thanks

In a worksheet formula it would be


=SUMPRODUCT(--(B2:B349="COMPLETED"),--(C2:C349 < TODAY()))
Now, unfortunately sumproduct does not work the same way in VBA.
You have to wrap it in the Evaluate Function..

X = Evaluate("=SUMPRODUCT(--(B2:B349=""COMPLETED""),--(C2:C349 < TODAY()))")

 

[Human_doing]

Thanks so much for your help, however perhaps I didn't explain myself correctly - the code you have provided does work but results in cell value = FALSE. What I am looking for is for cell value to be = number of rows which match the two critiera.

Below is the code I'm using so far could you please take a look?

Thanks

'Populate Row F with totals data
    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 = X = Evaluate("=SUMPRODUCT(--(B2:B" & LRTotal & =""COMPLETED""),--(C2:C" & LRTotal & "< TODAY()))")
    End With