Can INDIRECT help here?

[Tigerexcel]

Book7
	B	C
2	Name	Sales
3	Tom	10
4	Tom	20
5	Tom Total	30
6	Nick	40
7	Nick	50
8	Nick	60
9	Nick Total	150
10	Grand Total	180
Sheet2

Received a file of which I've extracted a small part of. Problem is everything is hardcoded.
Can I use INDIRECT to help total the values, something like SUM(INDIRECT(B5&"Total")) to pick up the respective subtotals and place the overall total in the Grand Total cell?
Is there a better way to work out totals and quickly make the spreadsheet dynamic so that rows can be added/removed and totals update automatically?

 

[njimack]

=SUMIF(B2:B9,"*Total",C2:C9)

 

[Yongle]

How about

Book1
	A	B	C
1
2		Name	Sales
3		Tom	10
4		Tom	20
5		Tom Total	30
6		Nick	40
7		Nick	50
8		Nick	60
9		Nick Total	150
10		Grand Total	180
Sheet1

Cell Formulas
Range	Formula
C10	C10	=SUMIF(B3:B9,"*Total*",C3:C9)

 

[Tigerexcel]

Great solution Njimack and Yongle, that is a nice solution.
I wonder can cells C5 and C9 be converted to formulae via SUBSTITUTE or other functions? In other words, change them from hardcoded to actual formulae. I'm sure there's a VBA way of doing this, but is there another way just using Excel's functions?

 

[Yongle]

I do not really ike it, but is seems to work!

Book1
	A	B	C
1
2		Name	Sales
3		Tom	10
4		Tom	20
5		Tom Total	30
6		Nick	40
7		Nick	50
8		Nick	60
9		Nick Total	150
10		Grand Total	180
Sheet1

Cell Formulas
Range	Formula
C5, C9	C5	=SUMIF($B$3:$B$9,SUBSTITUTE(B5," Total",""),$C$3:$C$9)
C10	C10	=SUMIF(B3:B9,"*Total*",C3:C9)

 

[njimack]

A better solution would be to delete those hard-coded total rows, and use the built-in Subtotal function.

 

[Tigerexcel]

I do not really ike it, but is seems to work!

Book1
	A	B	C
1
2		Name	Sales
3		Tom	10
4		Tom	20
5		Tom Total	30
6		Nick	40
7		Nick	50
8		Nick	60
9		Nick Total	150
10		Grand Total	180
Sheet1

Cell Formulas
Range	Formula
C5, C9	C5	=SUMIF($B$3:$B$9,SUBSTITUTE(B5," Total",""),$C$3:$C$9)
C10	C10	=SUMIF(B3:B9,"*Total*",C3:C9)

I don't mind it but you have to know where the totals are. The file I'm working has a number of subtotals so will take a little time to find them all.