SUMPRODUCT

[Alonso]

Hi

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

but the formul
=--(SUMPRODUCT((Ref!CX2:CX1069=Prog!J5)*(Ref!CY2:CY1069=Prog!K5)*(Ref!DA2A1069=Prog!M5)*(Ref!DB2B1069)))

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??

 

[Max]

column DB contains text and numbers

Lightly tested here, this seems to work ok
=SUMPRODUCT((ref!CX2:CX1069=prog!J5)*(ref!CY2:CY1069=prog!K5)*(ref!DA2A1069=prog!M5)*ISNUMBER(ref!DB2B1069),ref!DB2B1069)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik

 

[JMB]

you should not need the isnumbe
=SUMPRODUCT((ref!CX2:CX1069=prog!J5)*(ref!CY2:CY1069=prog!K5)*(ref!DA2A1069=prog!M5),ref!DB2B1069)

 

[Max]

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
---

 

[Alonso]

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

again
it seems that the error is somewhere the last calculation

 

[JMB]

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.