give the total from the different table

mark692

Active Member
Joined
Feb 27, 2015
Messages
306
Office Version
  1. 2016
Platform
  1. Windows
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1
 

Some videos you may like

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.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
Joined
Feb 27, 2015
Messages
306
Office Version
  1. 2016
Platform
  1. Windows
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]
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Last edited:

mark692

Active Member
Joined
Feb 27, 2015
Messages
306
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Feb 27, 2015
Messages
306
Office Version
  1. 2016
Platform
  1. Windows
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,188
Messages
5,570,755
Members
412,340
Latest member
nikitesh95
Top