Sum a range of cells defined by a string

[Nerdio]

I have a table with a number of columns. I want to sum the values in one column over a range of rows determined by values in another column.

For example,

I have a worksheet called Fund

On a different worksheet (Calculations)
I find the minimum value in Column A and get a row number
I find the maximum value in Column B and get a row number

Say Minimum is on row 4 and maximum is on row 6

I want to perform this SUM.

SUM(Fund!$B$4:Fund!$B$6)

This works fine if I enter it literally, but of course I want to use the Minimum and Maximum values I found earlier. So, just to get this working and understand the steps, On my Calculations worksheet I have created this in Cell A1

Fund!$B$4:Fund!$B$6

This is a string (text), so to do the same sum as before I have used this formula

SUM(INDIRECT(A1))

This fails with a #REF! error.

This gives me the same error

=SUM(INDIRECT("Fund!$B$4:Fund!$B$6"))

The problem must be to do with the way I am expressing the range of cells for INDIRECT, but the range is the same as for the example where I just use SUM.

What am I doing wrong?
Thank you

 

[Radoslaw Poprawski]

I am unsure if I got this correctly.
so My understanding is that you want to add minimum Value from column A and Maximum Value from column B all from Calculation worksheet.

So why not use:

=SUM(MIN(Calculations!A1#);MAX(Calculations!B1#))

where:
Calculation sheet Example:

and Fund sheet formula:

you could also bypasss the sum function entirely:

=MIN(Calculations!A1#)+MAX(Calculations!B1#)

 

[PeteWright]

Book1
	A	B	C
1	7	8
2	21	23
3	9	2
4	1	33
5	12	15
6	14	42
7	6	17
8			117
Fund

Cell Formulas
Range	Formula
C8	C8	=SUM(A4:B6)

Book1
	A
1	117
Calculations

Cell Formulas
Range	Formula
A1	A1	=SUM(INDIRECT("Fund!A"&XMATCH(MIN(Fund!A1:A7),Fund!A1:A7,0,1)&":B"&XMATCH(MAX(Fund!B1:B7),Fund!B1:B7,0,1),TRUE))

=SUM(INDIRECT("Fund!A"&XMATCH(MIN(Fund!A1:A7),Fund!A1:A7,0,1)&":B"&XMATCH(MAX(Fund!B1:B7),Fund!B1:B7,0,1),TRUE))

Is that what you need

 

[Nerdio]

I am unsure if I got this correctly.
so My understanding is that you want to add minimum Value from column A and Maximum Value from column B all from Calculation worksheet.

So why not use:

=SUM(MIN(Calculations!A1#);MAX(Calculations!B1#))

where:
Calculation sheet Example:
View attachment 106634

and Fund sheet formula:
View attachment 106635

you could also bypasss the sum function entirely:

=MIN(Calculations!A1#)+MAX(Calculations!B1#)

View attachment 106636

Not quite. I don't think my question was completely clear.
I want to sum the range on the Fund worksheet between the minimum value in Column A of that worksheet and the maximum value in column A of that worksheet.

So, if my data looks like this on the Fund Worksheet

The minimum value in Column A is in Row 4. The maximum value in Column A is in Row 6

I want to sum the values from B4 to B6. Effectively this

SUM(Fund!$B$4:Fund!$B$6)

 

[Nerdio]

Book1
	A	B	C
1	7	8
2	21	23
3	9	2
4	1	33
5	12	15
6	14	42
7	6	17
8			117
Fund

Cell Formulas
Range	Formula
C8	C8	=SUM(A4:B6)

Book1
	A
1	117
Calculations

Cell Formulas
Range	Formula
A1	A1	=SUM(INDIRECT("Fund!A"&XMATCH(MIN(Fund!A1:A7),Fund!A1:A7,0,1)&":B"&XMATCH(MAX(Fund!B1:B7),Fund!B1:B7,0,1),TRUE))

=SUM(INDIRECT("Fund!A"&XMATCH(MIN(Fund!A1:A7),Fund!A1:A7,0,1)&":B"&XMATCH(MAX(Fund!B1:B7),Fund!B1:B7,0,1),TRUE))

Is that what you need

Yes, that did exactly what I wanted. Thank you.