sum with multiple vlookups

[vijay.vizzu]

Dear All,


I wrote a formula like this" =IFERROR(SUM(IF($X3=TRUE,VLOOKUP($X$2,tbl_1,3,FALSE)*K3)),IF($Y3=TRUE,VLOOKUP($Y$2,tbl_1,3,FALSE)*L3),IF($Z3=TRUE,VLOOKUP($Z$2,tbl_1,3,FALSE)*M3),IF($AA3=TRUE,VLOOKUP($AA$2,tbl_1,3,FALSE)*N3),IF($AB3=TRUE,VLOOKUP($AB$2,tbl_1,3,FALSE)*O3),IF($AC3=TRUE,VLOOKUP($AC$2,tbl_1,3,FALSE)*P3),IF($AD3=TRUE,VLOOKUP($AD$2,tbl_1,3,FALSE)*Q3),IF($AE3=TRUE,VLOOKUP($AE$2,tbl_1,3,FALSE)*R3),IF($AF3=TRUE,VLOOKUP($AF$2,tbl_1,3,FALSE)*S3),IF($AG3=TRUE,VLOOKUP($AG$2,tbl_1,3,FALSE)*T3),IF($AH3=TRUE,VLOOKUP($AH$2,tbl_1,3,FALSE)*U3),IF($AI3=TRUE,VLOOKUP($AI$2,tbl_1,3,FALSE)*V3)),"")

I am using check box for true and false, if i will wrote IF($X3=TRUE,VLOOKUP($X$2,tbl_1,3,FALSE)*K3 = then it will blank due to error. becoz K3 cell is blank value, if i will put 1 there, then it works fine. so how can i solve this problem, whenever the cell will be blank.

Any help will be appreciated

 

[Faseeh]

Hi vijay.vizzu,


Replace simple

K3 with [code]IF((ISBLANK)K30=TRUE,1,K3)

, It will give you 1 if K3 is blank other wise will give the contained value of K3. BTW this formula could possibly be smarter if you give some detials of sheet.


I assume this should also work:

SUMPRODUCT((X3:AI3="TRUE&#34*(LOOKUP_COLUMN)*(X2:A12)*(K3:V3))[/code]


Regards,

Faseeh