SUMPRODUCT Assistance
 

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

SUMPRODUCT Assistance
 

Thing this works

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

assumes B31 = B29 + 30


VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=566149

SUMPRODUCT Assistance
 

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

"Barb Reinhardt" wrote:

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

SUMPRODUCT Assistance
 

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.

"Barb Reinhardt" wrote:

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