UDF or SUMPRODUCT with 51 named ranges?

[RyanH]

I need a formula that will sum the numbers in Col.V, if Col.T <= J4 AND Col.U
= "X". For example, if J4=10/1/08 the formula should = 5.50.

The data is in a sheet named "Global Schedule"
Col.T Col.U Col.V
9/1/08 X 2.00
9/10/08 X 3.50
9/20/08 1.50
10/3/08 X 5.00

This formula works perfect in 2007, but I get an error in 2003. I guess you
can't use entire columns in SUMPRODUCT in 2003.
=SUMPRODUCT(--('Global Schedule'!T:T<=$J$4),--('Global
Schedule'!U:U="X"),'Global Schedule'!V:V)

I used entire columns because I am constantly changing the number of rows in
'Global Schedule' worksheet. So I basically need to have a dynamic range.
So, Gary Keramidas suggested I name the ranges. Like this:
Insert>Name>Define
Name: EngineeringDates
Refers to:
=OFFSET('Global Schedule'!T3,0,0,COUNTA('Global Schedule'!$A:$A),1)

Name: EngineeringIndicators
Refers to:
=OFFSET('Global Schedule'!U3,0,1,COUNTA('Global Schedule'!$A:$A),1)

Name: EngineeringHours
Refers to:
=OFFSET('Global Schedule'!V3,0,0,COUNTA('Global Schedule'!$A:$A),1)

Then use Formula:
=SUMPRODUCT(--(EngineeringDates<=J4),--(EngineeringIndicators="X",
EngineeringHours)

This formula works great, but I was concerned because I have to do this 16
more times, for each department we have at our company. Will this cause a
memory problem and cause the application to run slow? I assume all these
Names are stored in Memory (RAM), right?

Would it be more efficient, compute faster, and use less memory if I had a
UDF?

 

[JP]

SUMPRODUCT is better than the alternative worksheet solution (arrays).
A UDF would be marginally slower, because writing the result to Excel
from VBA is somewhat costly. But you could write VBA code to paste in
the formula 16 times, just changing the inputs

--JP

 

[Fred Smith]

Just change the range to encompass the entire column, as in:
=SUMPRODUCT(--('Global Schedule'!T1:T65536<=$J$4),--('Global
Schedule'!U1:U65536="X"),'Global Schedule'!V1:V65536)

Then it will work in both 2003 and 2007.

Regards,
Fred.

 

[RyanH]

Fred, I wish it was that simple. Problem is when I delete rows the the max
row number gets smaller, eventually sabotaging the formula. For example,
T3:T65000 will change to T3:64775 if i delete 25 rows.

JP, how could I write the UDF in VBA. Here is a rough idea of what I would
be looking for: I know this doesn't work.


Public Function ScheduledHrs(DateColumn As String, Criteria1 As String, _
IndicatorColumn As String, Criteria2 As
String, _
HoursColumn As String)

'DateColumn = Would be the Col. Letter the Dates are located in
'Criteria1 = "<=$J$4"
'IndicatorColumn = Would be the Col. Letter that contains "X" 's
'Criteria2 = "=X"
'HoursColumn = Would be the Col. Letter where the dept. hours are located.

Dim wksGlobal As Worksheet
Dim lngLastRow As Long
Dim rngDates As Range
Dim rngIndicators As Range
Dim rngHours As Range

Set wksGlobal = Sheets("Global Schedule")
lngLastRow = wksGlobal.Cells(Rows.Count, "A").End(xlUp).Row

Set rngDates = wksGlobal.Range(Cells(3, DateColumn), Cells(lngLastRow,
DateColumn))
Set rngIndicators = wksGlobal.Range(Cells(3, IndicatorColumn),
Cells(lngLastRow, IndicatorColumn))
Set rngHours = wksGlobal.Range(Cells(3, HoursColumn), Cells(lngLastRow,
HoursColumn))

ScheduledHrs = WorksheetFunction.SumProduct(--(rngDates & Criteria1), _
--(rngIndicators &
Criteria2), _
rngHours)

End Function
--
Cheers,
Ryan

Just change the range to encompass the entire column, as in:
=SUMPRODUCT(--('Global Schedule'!T1:T65536<=$J$4),--('Global
Schedule'!U1:U65536="X"),'Global Schedule'!V1:V65536)

Then it will work in both 2003 and 2007.

Regards,
Fred.

 

[JP]

Looks alright to me, but I'll take a look at it again in the morning.

--JP

 

[JMB]

If you write it like this, your range reference won't change as you
insert/delete rows.
=SUM(A1:INDEX(A:A,65535))

Fred, I wish it was that simple. Problem is when I delete rows the the max
row number gets smaller, eventually sabotaging the formula. For example,
T3:T65000 will change to T3:64775 if i delete 25 rows.

JP, how could I write the UDF in VBA. Here is a rough idea of what I would
be looking for: I know this doesn't work.


Public Function ScheduledHrs(DateColumn As String, Criteria1 As String, _
IndicatorColumn As String, Criteria2 As
String, _
HoursColumn As String)

'DateColumn = Would be the Col. Letter the Dates are located in
'Criteria1 = "<=$J$4"
'IndicatorColumn = Would be the Col. Letter that contains "X" 's
'Criteria2 = "=X"
'HoursColumn = Would be the Col. Letter where the dept. hours are located.

Dim wksGlobal As Worksheet
Dim lngLastRow As Long
Dim rngDates As Range
Dim rngIndicators As Range
Dim rngHours As Range

Set wksGlobal = Sheets("Global Schedule")
lngLastRow = wksGlobal.Cells(Rows.Count, "A").End(xlUp).Row

Set rngDates = wksGlobal.Range(Cells(3, DateColumn), Cells(lngLastRow,
DateColumn))
Set rngIndicators = wksGlobal.Range(Cells(3, IndicatorColumn),
Cells(lngLastRow, IndicatorColumn))
Set rngHours = wksGlobal.Range(Cells(3, HoursColumn), Cells(lngLastRow,
HoursColumn))

ScheduledHrs = WorksheetFunction.SumProduct(--(rngDates & Criteria1), _
--(rngIndicators &
Criteria2), _
rngHours)

End Function