sum of lookup values(vlookup)
 

hi...

How we bring multiple columns from lookup table to single cell by adding all
the lookup values?

Ex:

I have sheet1 consisting the errors details of the all employees like this...

A1 A2 A3 A4 A5
1 emp Id 1st week 2nd week 3rd week 4th week
2 805033 3 0 1 4
3 805024 4 1 2 0
4 805036 4 1 0 1
5 805042 0 2 0 0
-------------------------------------------------------------------------------
in sheet2 I need to lookup each employee with total errors in a month i.e.
1st week+2nd week+3rd week....

so The result should lookup one employee errors by adding all errors.

the result should be like this for above example:

A1 A2
1 emp Id total errors
2 805033 8
3 805024 7
4 805036 6
5 805042 2

thanks in advance.

sum of lookup values(vlookup)
 

Hi,

Try something like:

=SUMPRODUCT((A1:A10=805033)*B1:E10)
or you can use the employee id cell
=SUMPRODUCT((A1:A10=F1)*B1:E10)

HTH
Jean-Guy

"Narendra Boga" wrote:

hi...

How we bring multiple columns from lookup table to single cell by adding all
the lookup values?

Ex:

I have sheet1 consisting the errors details of the all employees like this...

A1 A2 A3 A4 A5
1 emp Id 1st week 2nd week 3rd week 4th week
2 805033 3 0 1 4
3 805024 4 1 2 0
4 805036 4 1 0 1
5 805042 0 2 0 0
-------------------------------------------------------------------------------
in sheet2 I need to lookup each employee with total errors in a month i.e.
1st week+2nd week+3rd week....

so The result should lookup one employee errors by adding all errors.

the result should be like this for above example:

A1 A2
1 emp Id total errors
2 805033 8
3 805024 7
4 805036 6
5 805042 2

thanks in advance.

sum of lookup values(vlookup)
 

thanks Jean-Guy. I got it.
But I wanna this in Vlookup. Bcz I have large data with totaal 56 weeks
errors data.
sometimes I need to calculate total errors done by employe in 1st week in
every month or only 4th week of every month.

Sometimes I may need only sum of selected weeks errors (like 1st week and
3rd week)

thanks for your help......


"Jean-Guy" wrote:

Hi,

Try something like:

=SUMPRODUCT((A1:A10=805033)*B1:E10)
or you can use the employee id cell
=SUMPRODUCT((A1:A10=F1)*B1:E10)

HTH
Jean-Guy

"Narendra Boga" wrote:

hi...

How we bring multiple columns from lookup table to single cell by adding all
the lookup values?

Ex:

I have sheet1 consisting the errors details of the all employees like this...

A1 A2 A3 A4 A5
1 emp Id 1st week 2nd week 3rd week 4th week
2 805033 3 0 1 4
3 805024 4 1 2 0
4 805036 4 1 0 1
5 805042 0 2 0 0
-------------------------------------------------------------------------------
in sheet2 I need to lookup each employee with total errors in a month i.e.
1st week+2nd week+3rd week....

so The result should lookup one employee errors by adding all errors.

the result should be like this for above example:

A1 A2
1 emp Id total errors
2 805033 8
3 805024 7
4 805036 6
5 805042 2

thanks in advance.

sum of lookup values(vlookup)
 

Hi,

You can modify the formula I gave you to do that:

=SUMPRODUCT((A2:A10=F1)*(B1:E1="2nd week")*B2:E10)
where A2:A10 are ID numbers and B1:E1 are weeks title
use a cell to specify the week you want to total.

HTH
Jean-Guy
"Narendra Boga" wrote:

thanks Jean-Guy. I got it.
But I wanna this in Vlookup. Bcz I have large data with totaal 56 weeks
errors data.
sometimes I need to calculate total errors done by employe in 1st week in
every month or only 4th week of every month.

Sometimes I may need only sum of selected weeks errors (like 1st week and
3rd week)

thanks for your help......


"Jean-Guy" wrote:

Hi,

Try something like:

=SUMPRODUCT((A1:A10=805033)*B1:E10)
or you can use the employee id cell
=SUMPRODUCT((A1:A10=F1)*B1:E10)

HTH
Jean-Guy

"Narendra Boga" wrote:

hi...

How we bring multiple columns from lookup table to single cell by adding all
the lookup values?

Ex:

I have sheet1 consisting the errors details of the all employees like this...

A1 A2 A3 A4 A5
1 emp Id 1st week 2nd week 3rd week 4th week
2 805033 3 0 1 4
3 805024 4 1 2 0
4 805036 4 1 0 1
5 805042 0 2 0 0
-------------------------------------------------------------------------------
in sheet2 I need to lookup each employee with total errors in a month i.e.
1st week+2nd week+3rd week....

so The result should lookup one employee errors by adding all errors.

the result should be like this for above example:

A1 A2
1 emp Id total errors
2 805033 8
3 805024 7
4 805036 6
5 805042 2

thanks in advance.