PDA

View Full Version : Solved: Sumproduct with HLookup



JimS
07-14-2011, 01:53 PM
I'm trying to use a HLookup formula as one of the criteria in a SumProduct formula.

This formula works fine:
=SUMPRODUCT((Status="Won")*(District={"D1","D2","D3"}))

I would like to convert this working formula to this:
'=SUMPRODUCT((Status="Won")*(HLOOKUP(dSelector,dTable,17)))

Where row 17 of the HLookup match contains the following:
District={"D1","D2","D3"}

In using the Formula Evaluation the HLOOKUP produces this:
"District={""D1"",""D2"",""D3""}" which does not work.

Status and District are range names.

Any ideas?

Thanks...

JimS

Brandtrock
07-14-2011, 02:10 PM
Not tested, but you could try nesting your HLOOKUP in a REPLACE where you replace double quotes with single quotes.

Aflatoon
07-15-2011, 02:38 AM
That won't work - you cannot build up a formula string like that unless you have some means of evaluating it, such as the Evaluate function in VBA or the XLM function Evaluate. If you use the XLM function, you cannot use it directly in a cell, so you would need to define a name that uses it and evaluates the whole formula.
In my opinion, it would be far easier to restructure your criteria to match against a range of cells rather than trying to create a criteria string using formulas.

JimS
07-15-2011, 08:14 AM
You lost me (not hard to do though). If my only option is to use vba to create the formula I'll have to do it, but I'm not sure how to accomplish that - meaning creating the formula in vba while referncing a named range cell. What does the "Evaluate function in VBA" do?

Aflatoon
07-15-2011, 08:22 AM
The Evaluate function literally evaluates a formula string. So for this case, something like:

Function EvaluateFormula(strFormula as string)
Application.Volatile True
EvaluateFormula = application.caller.worksheet.evaluate(strFormula)
End Function

and then in the cell you would use:
=EvaluateFormula("SUMPRODUCT((Status=""Won"")*("&HLOOKUP(dSelector,dTable,17)&")")

Personally, I would not recommend it though. I would suggest you rethink the layout of the sheet so you don't have to try and create the criteria as text.

JimS
07-15-2011, 08:32 AM
Thanks...

What if I simply pasted the formula in with something like this:


Sub Macro1()
Dim f, x, y, z As String
x = Range("disF1").Value
y = "=SUMPRODUCT((Status=""Won"")*(Split)*"
z = y & x
f = z
Sheets("Summary").Select
Range("N3").Select
Range("N3").Formula = f
Range("N4").Select
End Sub

It doesn't like the "Range("N3").Formula = f " line.

Aflatoon
07-15-2011, 08:36 AM
What is the exact value of f at the time? Based on your initial post, I guess you are missing a closing parenthesis (or pairs of parentheses).

JimS
07-15-2011, 09:11 AM
You were correct - forgot 1 parentheses.

Thanks for all your help...

JimS

JimS
07-15-2011, 09:12 AM
You were correct - I forgot one ")".

Thanks for all your help...

JimS