SUMPRODUCT Assistance

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Barb Reinhardt - 28 Jul 2006 20:23 GMT

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

Reply to this Message

VBA Noob - 28 Jul 2006 20:39 GMT

Thing this works

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

assumes B31 = B29 + 30

VBA Noo

Reply to this Message

Toppers - 28 Jul 2006 20:49 GMT

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

> I am having difficulty wrapping my brain around this one.
>
[quoted text clipped - 13 lines]
> Thanks,
> Barb Reinhardt

Reply to this Message

Miguel Zapico - 28 Jul 2006 20:54 GMT

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.

> I am having difficulty wrapping my brain around this one.
>
[quoted text clipped - 13 lines]
> Thanks,
> Barb Reinhardt

Reply to this Message