Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]() 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 |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]() 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 |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Adding "OR" to a Sumproduct Formula | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |