SUMPRODUCT in Code 4

[SkipVought]

What am I missing?

Take a simple table like
[tt]
Name Amt
Skip 1
Mary 2
Fred 3
Skip 4
Fred 5
Mary 6
Skip 7
[/tt]

    Dim n
    n = Application.SumProduct([b](Range("Name") = "Skip")[/b] * (Range("Amt")))

I get Type Mismatch.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[PHV]

Just a guess:
n = Application.SumProduct((Range("Name") = "Skip"), (Range("Amt")))
Or:
n = Application.Sum((Range("Name") = "Skip") * (Range("Amt")))

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

 

[SkipVought]

PHV, Thanks for the try -- but no dice! :-(

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[Zathras]

I think I see what you're driving at. Both of these work directly in a worksheet:
[tt]
=SUMPRODUCT((Name="Skip")*Amt)
=SUMIF(Name,"Skip",Amt)
[/tt]
but only this one works in VBA:

Sub Test()
Dim n
  n = WorksheetFunction.SumIf([Name], "Skip", [Amt])
MsgBox n
End Sub

It seems that this one should work, but it doesn't (Excel 97 SR-2):

Sub Test2()
Dim n
  n = WorksheetFunction.SumProduct(([Name] = "Skip") * [Amt])
MsgBox n
End Sub

I never really liked SUMPRODUCT for this sort of thing anyway.

 

[PHV]

In fact the problem is here:
(Range("Name") = "Skip")

I guess it should be a FormulaArray ?

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

 

[SkipVought]

Thanks Z,

I am attempting to replace several complex formulas with UDF's. This one is the least complex, so i might just forget this entire thing if it looks like more effort than its worth.

So I want to replace this...
[tt]
=IF(SUMPRODUCT((PART_ID=$A3)*(MDCN=$L3)*(PCT<=EndCalDate)*(QTY))>B3,B3,SUMPRODUCT((PART_ID=$A3)*(MDCN=$L3)*(PCT<=EndCalDate)*(QTY)))
[/tt]
with something like...

Function EDQ(sPN As String, sMDCN As String, iRQ As Integer) As Integer
    Dim n
    With wsSprOrds
        n = Application.SumProduct( _
            (.Range("PART_ID") = sPN) * _
            (.Range("MDCN") = sMDCN) * _
            (.Range("PCT") <= Range("EndCalDate")) * _
            (.Range("QTY")))
    End With
    If n > iRQ Then
        EDQ = iRQ
    Else
        EDQ = n
    End If
End Function

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[Zathras]

Pity. However, a "proper" UDF to replace that would require more parameters. Otherwise it would be a kludge with direct references to parts of the worksheet that aren't explicitly identified in the parameter list.

You could still simplify the formulas, however. You could use a formula that looks like this:
[tt]
=MIN(DSUM(database,4,criteria),B3)
[/tt]
by simply setting up the critera range thus:
[tt]
PART_ID MDCN
=$A$3 =$L$3 =PCT<EndCalDate
[/tt]
(Leave the cell above "=PCT<EndCalDate" blank.)

In fact, you could at least get a little simplification by replacing the IF structure with MIN:
[tt]
=MIN(SUMPRODUCT((PART_ID=$A3)*(MDCN=$L3)*(PCT<=EndCalDate)*(QTY)),B3)
[/tt]

 

[SkipVought]

Z,

I accept your castigation regarding a PROPER UDF!

I'll check out your suggestions.

Thanx!

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[xlbo]

Pretty certain it throws an error as the way we use SumProduct is not actually the way it was designed to be used.

If you notice, the arguments for Sumproduct are:

=sumproduct(array1,array2...etc etc

in formulae, we can enter a range and have it expressed as an array - I have a feeling you cannot implicitly do the same in code - we are also corrupting the actual syntax of the formula as well so I am not that surprised that it doesn't like it in code

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[comaboy]

hmmm... Probably missing something here, but wouldn't

=SUMIF(A1:A7,"Skip",B1:B7)
Do the job?

 

[xlbo]

Skip - from another source (didn't work this out for myself) - the following works:

Sub test()
x = ActiveSheet.[b]Evaluate("[/b]SumProduct((srNames=""Skip"")*(srAmt))[b]")[/b]
End Sub

where 'srNames' is a named range, as is 'srAmt'

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Geoff,

Thanks!

U da man!

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[GlennUK]

Hey Geoff, well found ... I'll be noting that for future use. Have a ---> *


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

Actually, guys, my EXAMPLE was much simpler than real-life..

As my code indicates, I have FOUR ranges, THREE of which have criteria. So a SUMIF, for instance, would not suffice.

Thanx to all.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[xlbo]

Cheers guys but you can thank a chap called Bob Phillips on the Excel-L list for this little gem - funnily enough (happens a lot actually), someone had posted a very similar question to Skip's so I just checked the answer out and it worked.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[comaboy]

aaaah.. You didn't mention that in the OP thought it seemed too simple an answer!