Summing values from various sheets in a file based on employee id

[sivasankar]

Hi,

Please find the following data of 12 sheets (12 months) and i have named each sheet like jan, feb, mar, .....& Dec.

31 Gross Salary per Month Earnings per Month Deductions Net Salary Emp Code Location NAME OF THE EMPLOYEE Present Days Basic HRA Con Allow Medical Reimb Uniform Allow Spl Allow Total Basic HRA Con. Allow Medical Reimb Uniform Allow Spl Allow Total PF PT TDS Total 100002 Banglore Praveen 31 15363 7682 800 1250 1000 2831 28926 15363 7682 800 1250 1000 2831 28926 780 200 0 980 27946 100003 Hyderabad Siva 30 85338 42669 800 1250 2500 25321 157878 82585 41293 774 1210 2419 24504 152785 780 200 0 980 151805 100004 Hyderabad sankar 25 57390 28695 800 1250 2500 15022 105657 46282 23141 645 1008 2016 12115 85207 780 200 10000 10980 74227 100005 Banglore Rao 20 102405 51203 800 1250 2500 31612 189770 66068 33034 516 806 1613 20395 122432 780 200 57068 58048 64384 100006 Banglore Kumar 31 15959 7980 800 1250 1000 3486 30475 15959 7980 800 1250 1000 3486 30475 780 200 0 980 29495 100007 Indore hari 31 57390 28695 800 1250 2500 15022 105657 57390 28695 800 1250 2500 15022 105657 780 200 8696 9676 95981 <colgroup><col><col><col><col span="2"><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col span="2"><col></colgroup><tbody> </tbody>

<colgroup><col><col><col><col span="2"><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col><col><col><col></colgroup><tbody>
</tbody>

And i am trying to add the values based on emp code from all sheets into another sheet. I have tried with VLOOKUP and its working if the emp code is exists in those months / sheets. if emp code is not there it is giving error #N/A.

the code which i have tried is

=+VLOOKUP(B7,apr,26,FALSE)+VLOOKUP(B7,may,26,FALSE)+VLOOKUP(B7,jun,26,FALSE)+VLOOKUP(B7,Jul,26,FALSE)+VLOOKUP(B7,Aug,26,FALSE)+VLOOKUP(B7,Sep,26,FALSE)+VLOOKUP(B7,Oct,26,FALSE)+VLOOKUP(B7,Nov,26,FALSE)+VLOOKUP(B7,Dec,26,FALSE)+VLOOKUP(B7,Jan,26,FALSE)+VLOOKUP(B7,Feb,26,FALSE)+VLOOKUP(B7,Mar,26,FALSE)

please help me in simplifying the code.

thanks in Advance.

Siva Sankar

 

[Aladin Akyurek]

Have a look at: http://www.mrexcel.com/forum/excel-questions/119020-sumif-multiple-sheets.html

 

[sivasankar]

Thanks a Lot. I have tried Option 1 and its working.

 

[Aladin Akyurek]

Thanks a Lot. I have tried Option 1 and its working.

You are welcome. That's great to hear.