SUMPRODUCT Assistance

[Guest]

I am having difficulty wrapping my brain around this one.

I have this equation:

=SUMPRODUCT(--($B$43:$B$7577>=DATE(YEAR($B$29),MONTH($B$29),DAY($B$29)-B$31)),--($F$43:$F$7577="Open"))

The date of an activity is in B43:B7557 and values of Open are in F43:F7577
The date of the "report" is in B29.

In B31, I have 30 (as in 30 days)

What I want to do is count the number of activities that are over 30 days
late, ... over 60 days late (from C31), over 90 days late (from D31) etc.
What do I need to change.

Thanks,
Barb Reinhardt

 

[VBA Noob]

Thing this works

=SUMPRODUCT(--($B$43:$B$7577>B31)*(--($F$43:$F$7577<>"")))

assumes B31 = B29 + 30


VBA Noo

 

[Guest]

Try:

=SUMPRODUCT(--($B$43:$B$7577<=$B$29-B$31),--($B$43:$B$7577>$B$29-C$31),--($F$43:$F$7577="Open"))

for late by >=30 and < 60

=SUMPRODUCT(--($B$43:$B$7577<=$B$29-C$31),--($B$43:$B$7577>$B$29-D$31),--($F$43:$F$7577="Open"))

for late by >=60 and < 90

=SUMPRODUCT(--($B$43:$B$7577<=$B$29-D$31),--($F$43:$F$7577="Open"))

for late by > 90

HTH

 

[Guest]

The sintaxis of the formula is correct, you just need to change the >= to <,
if you want the dates on that columns that are open more than 30/60/90 days
before your current date
=SUMPRODUCT(--($B$43:$B$7577<DATE(YEAR($B$29),MONTH($B$29),DAY($B$29)-B$31)),--($F$43:$F$7577="Open"))

Hope this helps,
Miguel.