how to use the sum function through indirect or vlookup?

[skyep2058]

So I have attached my sheet.

So I wrote the formula on R19 on the Power Level page of the attached sheet like this:

=SUM((VLOOKUP(E8, INDIRECT($B$7&"!B12:Q194"), 16, 0))VLOOKUP(H8, INDIRECT($B$7&"!B12:Q194"), 16, 0)))

For some reason the sum formula wont accept the 2 values to add everything inbetween from a separate sheet. How can I do this? I'm trying to see how much it would be TOTAL for the user to add in order to power level his great building from one level to another, and I depicted those 2 levels as cells: E8, and H8. So in this case from level 20 to level 74, and it should add ALL the values between the cells of those levels from the separate sheet of CE or whatever page I choose.

Any ideas?

[Bo_Ry]

Please try

=SUM(INDEX(INDIRECT($B$7&"!Q12:Q194"),MATCH(E8,INDIRECT($B$7&"!B12:b194"),)):INDEX(INDIRECT($B$7&"!Q12:Q194"),MATCH(H8,INDIRECT($B$7&"!B12:b194"),)))

[skyep2058]

It gave me a value. Ill check to see if its working correctly. I had to add ROUND before SUM thought cuz it was giving me a decimal which I don't want.

[skyep2058]

Appears to work as intended. Anyway to explain it to me?! I Understand the indirect function, what is match and index doing?

[Bo_Ry]

Match
https://support.office.com/en-us/art...rs=en-US&ad=US

Index
https://support.office.com/en-us/art...rs=en-US&ad=US

The formula can be shortened to

=SUM(INDEX(INDIRECT($B$7&"!Q12:Q194"),E8):INDEX(INDIRECT($B$7&"!Q12:Q194"),H8))


=VLOOKUP(E8,INDIRECT($B$7&"!B12:Q194"),16,0)
=INDEX(INDIRECT($B$7&"!Q12:Q194"),E8)
For value, both work very similarly and give the value of 374.5 but in addition to value Index also give cell address.

=INDEX(INDIRECT($B$7&"!Q12:Q194"),E8)
=CE!Q31 =374.5

Combined with another index with ":"
=INDEX(INDIRECT($B$7&"!Q12:Q194"),E8):INDEX(INDIRECT($B$7&"!Q12:Q194"),H8)
=CE!Q31:CE!Q85
This gives the array of value from Q31 to Q85

=SUM(INDEX(INDIRECT($B$7&"!Q12:Q194"),E8):INDEX(INDIRECT($B$7&"!Q12:Q194"),H8))
=SUM(CE!Q31:CE!Q85)

=10002.5

[skyep2058]

Thank you Bo Ry! A lot of stuff but I super appreciate it!