adding values of vlookup's

  • Thread starter Thread starter 11
  • Start date Start date
1

11

Hi,

Even though I knew I'm not good on formulas, I tried to come of with
some and "surprisingly" it didn't worked out as desired

Below is the formula,

=VLOOKUP(E11,$U$10:$Z$11,2,0)
+VLOOKUP(F11,$U$10:$Z$11,3,0)
+VLOOKUP(G11,$U$10:$Z$11,4,0)
+VLOOKUP(H11,$U$10:$Z$11,5,0)
+VLOOKUP(I11,$U$10:$Z$11,6,0)

Help me pls...

Vinu.
 
Have you looked to see what each VLOOKUP does on its own.
If one of them is #N/A, try
=sum(
VLOOKUP(E11,$U$10:$Z$11,2,0)
,VLOOKUP(F11,$U$10:$Z$11,3,0)
,VLOOKUP(G11,$U$10:$Z$11,4,0)
,VLOOKUP(H11,$U$10:$Z$11,5,0)
,VLOOKUP(I11,$U$10:$Z$11,6,0)
)
best wishes
 
Are you sure you're using the right function for what you want to do?

Here's how your formula works...
=VLOOKUP(E11,$U$10:$Z$11,2,0)
+VLOOKUP(F11,$U$10:$Z$11,3,0)
+VLOOKUP(G11,$U$10:$Z$11,4,0)
+VLOOKUP(H11,$U$10:$Z$11,5,0)
+VLOOKUP(I11,$U$10:$Z$11,6,0)
Click to expand...

Look at U10:Z10, if it equals E11 then add the value of column V to...

Look at U10:Z10, if it equals F11 then add the value of column W to...

Look at U10:Z10, if it equals G11 then add the value of column X to...

Look at U10:Z10, if it equals H11 then add the value of column Y to...

Look at U10:Z10, if it equals I11 then add the value of column

Maybe this is what you want:

=SUMPRODUCT(SUMIF(V10:Z10,E11:I11,V11:Z11))
 
Hi,

Thanks a many for helping me but still
i'm not geeting the corret reselt.
May be the falt if with me as i didn't explined much.

The data in $U$10:$Z$11 will be like this,

NO 100 100 50 100 50
YES 0 0 0 0 0

i.e. If the E11, F11, G11 where YES and H11,I11 where NO then, in J11
the total has to be colculated(100+100+50+0+0)

Is there any way to get it..?! pls help.

Vinu.
 
NO 100 100 50 100 50
YES 0 0 0 0 0
the total has to be colculated(100+100+50+0+0)
Click to expand...

If that's the result you expect then you have Yes/No in the wrong positions.
I think it should be:

YES 100 100 50 100 50
NO 0 0 0 0 0

Bernard's suggestion should work.

Here's an alternative that needs to be array entered** :

=SUM(IF(E11:I11="yes",V10:Z10,V11:Z11))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

If a NO response will *always* equate to 0 as is shown in your sample data
then we can shorten that formula a bit:

=SUM(IF(E11:I11="yes",V10:Z10))
 
Hi Biff,

Bernard's suggestion was the perfect one if I change the possitions of
Yes/No.
But for this perticular data "NO" is the one having that values and
the YES value will always be "0".

Basicly it's an penalty tracker and if the person was not having the
perticlar thing he will be penalised with some amount and there is 5
things we are looking into and the disered formula will have to give
you the total amount of penalitioation.

So,

The data in $U$10:$Z$11 will be like this,


NO 100 100 50 100 50
YES 0 0 0 0 0


i.e. If the E11, F11, G11 where YES and H11,I11 where NO then, in J11
the total has to be colculated(100+100+50+0+0)


Pls advice....

Vinu.
 
Hi,

I succeeded by breaking the below formula in to 5 different cells and
adding them into the desired cell.
=VLOOKUP(E11,$U$10:$Z$11,2,0)
+VLOOKUP(F11,$U$10:$Z$11,3,0)
+VLOOKUP(G11,$U$10:$Z$11,4,0)
+VLOOKUP(H11,$U$10:$Z$11,5,0)
+VLOOKUP(I11,$U$10:$Z$11,6,0)

But it will be neat if it can be achieved in single formula strip..

Vinu.
 
What i'm doing here.. !!??

I just miss placed Bernard's suggested fourmula and came down to Biff
but the first one was the perfect remidy for my problem. :(

Sorry is the least i can say.

But I'm still wondaring around Biff's sugetion.

Thanks Thanks Thanks ...

Vinu.
 
Ok, but this setup doesn't make any sense to me!

What you want to do is if the cell entry is Yes then add the corresponding
value from the No row.

Array entered** :

=SUM(IF(E11:I11="yes",V10:Z10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Back
Top