merge amounts for each name based on adjacent cell contains TOTAL word

[Mussala]

Hi
I look for way to merge amounts for each name based on adjacent cell
in sheet1 every name in column B contains the total value in lastrow in column A contains TOTAL word and correspond amount in column E . so I want matching F2 in sheet2 with column B in sheet1, if it's matched then I want summing the amounts in cell for clumn E where is adjacent for TOTAL cell for the same name and show the result in F5 in sheet2 .
for instance :
Mussaila1=4900+300=5200

aw
	A	B	C	D	E
1	ITEM	NAME	INVOICE NO	ORDER NO	AMOUNT
2	1	Mussaila1	INA00	OR00	1000
3	2	Mussaila1	INA00	OR00	1200
4	3	Mussaila1	INA00	OR00	1300
5	4	Mussaila1	INA00	OR00	1400
6	TOTAL	Mussaila1	INA00	OR00	4900
7	1	Mussaila2	INA01	OR01	100
8	2	Mussaila2	INA01	OR01	200
9	TOTAL	Mussaila2	INA01	OR01	300
10	1	Mussaila1	INA02	OR02	300
11	TOTAL	Mussaila1	INA02	OR02	300
Sheet1

before

aw
	F
1	NAME
2	Mussaila1
3
4	AMOUNT
5
sheet2

should be

aw
	F
1	NAME
2	Mussaila1
3
4	AMOUNT
5	5200
sheet2

thank you

 

[Sufiyan97]

Try

Book2
	A	B	C	D	E	F	G	H	I	J
1	ITEM	NAME	INVOICE NO	ORDER NO	AMOUNT				NAME
2	1	Mussaila1	INA00	OR00	1000				Mussaila1
3	2	Mussaila1	INA00	OR00	1200
4	3	Mussaila1	INA00	OR00	1300				AMOUNT
5	4	Mussaila1	INA00	OR00	1400				5200
6	TOTAL	Mussaila1	INA00	OR00	4900
7	1	Mussaila2	INA01	OR01	100
8	2	Mussaila2	INA01	OR01	200
9	TOTAL	Mussaila2	INA01	OR01	300
10	1	Mussaila1	INA02	OR02	300
11	TOTAL	Mussaila1	INA02	OR02	300
12
13
14
Sheet1

Cell Formulas
Range	Formula
I5	I5	=SUMIFS($E$2:$E$11,$B$2:$B$11,I2,$A$2:$A$11,"<>Total")

 

[Mussala]

thank you ,but between two sheets , not in the same sheet!

 

[Sufiyan97]

Sheet1

Book2
	A	B	C	D	E	F
1	ITEM	NAME	INVOICE NO	ORDER NO	AMOUNT
2	1	Mussaila1	INA00	OR00	1000
3	2	Mussaila1	INA00	OR00	1200
4	3	Mussaila1	INA00	OR00	1300
5	4	Mussaila1	INA00	OR00	1400
6	TOTAL	Mussaila1	INA00	OR00	4900
7	1	Mussaila2	INA01	OR01	100
8	2	Mussaila2	INA01	OR01	200
9	TOTAL	Mussaila2	INA01	OR01	300
10	1	Mussaila1	INA02	OR02	300
11	TOTAL	Mussaila1	INA02	OR02	300
12
13
Sheet1

Sheet2

Book2
	D	E	F	G	H	I
1			NAME
2			Mussaila1
3
4			AMOUNT
5			5200
6
7
Sheet2

Cell Formulas
Range	Formula
F5	F5	=SUMIFS(Sheet1!$E$2:$E$11,Sheet1!$B$2:$B$11,F2,Sheet1!$A$2:$A$11,"<>Total")

 

[Mussala]

Awesome !
is there any way to specify the whole column instead of pull the range down?

 

[Sufiyan97]

I suggest to avoide using entire column as it leads to slow operation of data is large size.

Try

=SUMIFS(Sheet1!E:E,Sheet1!B:B,F2,Sheet1!A:A,"<>Total")

 

[Peter_SSs]

Seems more logical & efficient to me to add the rows where column A is "Total" rather than all the ones where it is not "Total".
I do agree that whole columns references should not be needed. Just choose a number big enough to cover ant data that you might ever have.

Mussala.xlsm
	A	B	C	D	E
1	ITEM	NAME	INVOICE NO	ORDER NO	AMOUNT
2	1	Mussaila1	INA00	OR00	1000
3	2	Mussaila1	INA00	OR00	1200
4	3	Mussaila1	INA00	OR00	1300
5	4	Mussaila1	INA00	OR00	1400
6	TOTAL	Mussaila1	INA00	OR00	4900
7	1	Mussaila2	INA01	OR01	100
8	2	Mussaila2	INA01	OR01	200
9	TOTAL	Mussaila2	INA01	OR01	300
10	1	Mussaila1	INA02	OR02	300
11	TOTAL	Mussaila1	INA02	OR02	300
Sheet1

Mussala.xlsm
	F
1	NAME
2	Mussaila1
3
4	AMOUNT
5	5200
Sheet2

Cell Formulas
Range	Formula
F5	F5	=SUMIFS(Sheet1!$E$2:$E$10000,Sheet1!$B$2:$B$10000,F2,Sheet1!$A$2:$A$10000,"Total")

 

[Mussala]

I suggest to avoide using entire column as it leads to slow operation of data is large size.

understood .
thank you for your help

 

[Mussala]

Seems more logical & efficient to me to add the rows where column A is "Total" rather than all the ones where it is not "Total".

that's why I mark solution .

I do agree that whole columns references should not be needed.

got it .
thank you so much .

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.