# give the total from the different table

#### mark692

##### Active Member
hi guys i have two tables in different sheet on sheet 1 is where i want to put the total of the amount that i input on table 2 on sheet 2 depending on name on column B and the header of the table, like the sample table below

ABCDEFGHI
1Table 1 on sheet 1
2123456
3Cash10050
4Receivable50
5Payable60
6
7
8Data on sheet 2
9123456
10Cash20
11Receivable60
12Payable
13Cash3050
14cash50
15Payable50
16

</tbody>
Sheet1

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

##### MrExcel MVP
Given: A:G of Sheet1 with A2 = Cash and A:G of Sheet2 with A2 = Cash...

In C2 of Sheet 1 enter, copy across, and down:

=SUMIFS(INDEX(Sheet2!\$A:\$G,0,MATCH(B\$1,INDEX(Sheet2!\$A:\$G,1,0),0)),Sheet2!\$A:\$A,\$A2))

#### mark692

##### Active Member
Given: A:G of Sheet1 with A2 = Cash and A:G of Sheet2 with A2 = Cash...

In C2 of Sheet 1 enter, copy across, and down:

=SUMIFS(INDEX(Sheet2!\$A:\$G,0,MATCH(B\$1,INDEX(Sheet2!\$A:\$G,1,0),0)),Sheet2!\$A:\$A,\$A2))

i tried your code sir on my file i paste your code and edit it according to my table but it give me error n/a here is the SS of my table in sheet 1
cell e 16 is where i want to put the code

[/URL][/IMG]

and here is the table in sheet 2

[/URL][/IMG]

##### MrExcel MVP
i tried your code sir on my file i paste your code and edit it according to my table but it give me error n/a here is the SS of my table in sheet 1
cell e 16 is where i want to put the code

[/URL][/IMG]

and here is the table in sheet 2

[/URL][/IMG]

An image is not much of a value, as it requires re-typing everything it shows.

See rather the workbook that implements the suggestion: https://www.dropbox.com/s/aqpwkg48ml0xgen/mark692%20sumifs.xlsx?dl=0

Last edited:

#### mark692

##### Active Member

An image is not much of a value, as it requires re-typing everything it shows.

See rather the workbook that implements the suggestion: https://www.dropbox.com/s/aqpwkg48ml0xgen/mark692 sumifs.xlsx?dl=0

Sir i get the reason on why it is not working on my file, the reasons are

1. the range on sheet 2 should be not in table. but the problem is i need the data to be in a table, because i need the filter and sort functions of table.

2. the second reason is that the header of the data on sheet 2 needs to be in Row 1, if you move it on row 2 the formula will not work,

#### mark692

Up

##### MrExcel MVP

I don't follow what you are saying.

If you want the SUMIFS to be more specific...

=SUMIFS(INDEX(Sheet2!\$A\$2:\$G\$7,0,MATCH(B\$1,Sheet2!\$A\$1:\$G\$1,0)),Sheet2!\$A\$2:\$A\$7,\$A2)

#### mark692

##### Active Member
I don't follow what you are saying.

If you want the SUMIFS to be more specific...

=SUMIFS(INDEX(Sheet2!\$A\$2:\$G\$7,0,MATCH(B\$1,Sheet2!\$A\$1:\$G\$1,0)),Sheet2!\$A\$2:\$A\$7,\$A2)

i have solved it sir, thanks

Replies
5
Views
137
Replies
0
Views
65
Replies
15
Views
228
Replies
0
Views
79
Replies
9
Views
216