Hi

I'm trying to do an advanced lookup that matches 3 criterias
so, i though i could use SUMPRODUCT

but the formula
=--(SUMPRODUCT((Ref!CX2:CX1069=Prog!J5)*(Ref!CY2:CY10 69=Prog!K5)*(Ref!DA2:DA1069=Prog!M5)*(Ref!DB2:DB10 69)))

returns a #VALUE!

the "calc steps" shows that the problem occurs prior the last calculation

column DB contains text and numbers
and i want/need to show that way


any ideas??

column DB contains text and numbers

Lightly tested here, this seems to work ok:
=SUMPRODUCT((ref!CX2:CX1069=prog!J5)*(ref!CY2:CY10 69=prog!K5)*(ref!DA2:DA1069=prog!M5)*ISNUMBER(ref! DB2:DB1069),ref!DB2:DB1069)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---
"Alonso" wrote:
I'm trying to do an advanced lookup that matches 3 criterias
so, i though i could use SUMPRODUCT

but the formula
=--(SUMPRODUCT((Ref!CX2:CX1069=Prog!J5)*(Ref!CY2:CY10 69=Prog!K5)*(Ref!DA2:DA1069=Prog!M5)*(Ref!DB2:DB10 69)))

returns a #VALUE!

the "calc steps" shows that the problem occurs prior the last calculation

column DB contains text and numbers
and i want/need to show that way

you should not need the isnumber
=SUMPRODUCT((ref!CX2:CX1069=prog!J5)*(ref!CY2:CY10 69=prog!K5)*(ref!DA2:DA1069=prog!M5),ref!DB2:DB106 9)

"Max" wrote:

column DB contains text and numbers

Lightly tested here, this seems to work ok:
=SUMPRODUCT((ref!CX2:CX1069=prog!J5)*(ref!CY2:CY10 69=prog!K5)*(ref!DA2:DA1069=prog!M5)*ISNUMBER(ref! DB2:DB1069),ref!DB2:DB1069)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---
"Alonso" wrote:
I'm trying to do an advanced lookup that matches 3 criterias
so, i though i could use SUMPRODUCT

but the formula
=--(SUMPRODUCT((Ref!CX2:CX1069=Prog!J5)*(Ref!CY2:CY10 69=Prog!K5)*(Ref!DA2:DA1069=Prog!M5)*(Ref!DB2:DB10 69)))

returns a #VALUE!

the "calc steps" shows that the problem occurs prior the last calculation

column DB contains text and numbers
and i want/need to show that way

you should not need the isnumber

Yes, you're right. It was overkill. Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---

Well
now instead of a #VALUE!
i get a zero 0

again
it seems that the error is somewhere the last calculation




"JMB" wrote:

you should not need the isnumber
=SUMPRODUCT((ref!CX2:CX1069=prog!J5)*(ref!CY2:CY10 69=prog!K5)*(ref!DA2:DA1069=prog!M5),ref!DB2:DB106 9)

Is all of column DB formatted as text? Locate a number in column DB and use
Isnumber(cellref) in an adjacent empty cell. What do you get? If the
numeric values in column DB are actually text, sumproduct will return 0. I
was assuming column DB was formatted as general and contained both text and
numeric data.


"Alonso" wrote:

Well
now instead of a #VALUE!
i get a zero 0

again
it seems that the error is somewhere the last calculation




"JMB" wrote:

you should not need the isnumber
=SUMPRODUCT((ref!CX2:CX1069=prog!J5)*(ref!CY2:CY10 69=prog!K5)*(ref!DA2:DA1069=prog!M5),ref!DB2:DB106 9)