Sum Values from different column

[Taha Sk]

Hi All,

I am trying to sum the values in B, C, D based on the value in column A
------------------------
Available Data
------------------------
Account no. Item1 Item2 Item3
1022 3 1
1023 2 2
1024 2 1

------------------------
What I need.
------------------------
In another sheet I have two table where I want to sum the value from specific column when I fill up the "Account no." column.

For E.g.
In Table 1 Sum the corresponding value from Item1 and Item3 Based on Value in Column "Account No.".

Account No. Sum of Items
1022 3
1023 4
1024 2

Similarly,
In Table 2 Sum the corresponding value from Item2 and Item3 Based on Value in Column "Account No.".

Account No. Sum of Items
1022 1
1023 2
1024 1

On a side note I tried a lot of googling could not find a similar solution. Below is current method which I feel is not very efficient and I believe you guys might have a better solution.

Current Method
Table1
Vlookup(item1) + Vlookup(Item3)

Any help on this is appreciated.

Regards
Taha

 

[Claus Busch]

Hi Taha,

Am Wed, 20 Nov 2013 05:00:12 -0800 (PST) schrieb Taha Sk:

In Table 1 Sum the corresponding value from Item1 and Item3 Based on Value in Column "Account No.".

Account No. Sum of Items
1022 3
1023 4
1024 2

your Account No in A2:A4
Then in B2 for Item1 and Item2:
=SUM(INDEX(Sheet1!B:C,MATCH(A2,Sheet1!$A$1:$A$4,0),))

In B2 for Item2 and Item3:
=SUM(INDEX(Sheet1!C,MATCH(A2,Sheet1!$A$1:$A$4,0),))


Regards
Claus B.