SUMPRODUCT return #ERROR!

[daniek]

Hi all
I'm using sumproduct to find the total amount who met a certain criteria in a schedule.

The following works perfectly

=SUMPRODUCT((amount)*(paid="X")*(Due_Date(E1,D1)<$I$10))

where amount and paid are all named ranges which are (currently) 1 column by 10 rows, 1-10.

However when I try to calculate using a range (Pdtype and LoadDate) with the Due_Date function I get #VALUE!

=SUMPRODUCT((amount)*(paid="X")*(Due_Date(Pdtype,LoadDate)<I10))

Pdtype and LoadDate are named ranges which are (currently) 1 column by 10 rows.

###### Due_Date Fucntion #############################
Function Due_Date(DPeriod As String, LDate As Date) As Date

Select Case DPeriod
Case Is = "S"
Due_Date = LDate + 7
Case Is = "D"
Due_Date = LDate + 14
Case Is = "M"
Due_Date = VBA.DateSerial(VBA.Year(LDate), VBA.Month(LDate) + 1, 0)
Case Is = "T"
Due_Date = VBA.DateSerial(VBA.Year(LDate), VBA.Month(LDate) + 2, 0)
Case Else
Due_Date = LDate + 7
End Select

End Function
########### End Due_Date Function ##################

Any suggestions, I feel like I'm missing something really obvious?

 

[PHV]

Perhaps this ?
Function Due_Date(rngDPeriod As Range, rngLDate As Range) As Date
Dim DPeriod As String, LDate As Date
DPeriod = rngDPeriod.Value
LDate = rngLDate.Value
Select Case DPeriod

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[daniek]

I've change the Due_Date module to the following

Function Due_Date(rngDPeriod As Range, rngLDate As Range) As Date
Dim DPeriod As String, LDate As Date
DPeriod = rngDPeriod.Value
LDate = rngLDate.Value
Select Case DPeriod

but I'm still receiving an #VALUE! error message.