# Using a value in a cell as a max range for a sum

#### sbozicevic

##### New Member
Hello!

I am trying to use the value of a cell to define my max range for other functions in other cells. I am working with a huge data set, and I want to optimize my processes.

Here is what I'm trying to do.

I have a cell which represents orders: H2
This cell counts the number of orders in my worksheet, let's say H2 = 100.

Since in practice, the actual number of orders is in the hundreds of thousands, I want to use the value of H2 to set the max range for future processes.

For example;
I have a large number of cells that reference the same worksheet, so it slows down performance to have each cell go to 1,000,000. I'd like to have them know to stop at the value of H2.
=SUM(IF(\$A5=raw!\$O\$2:\$O\$1000000,raw!\$F\$2:\$F\$1000000-raw!\$E\$2:\$E\$1000000,0))

So I'm trying to achieve:
=SUM(IF(\$A5=raw!\$O\$2:\$O\$[VALUE OF H2],raw!\$F\$2:\$F\$[VALUE OF H2]-raw!\$E\$2:\$E\$[VALUE OF H2],0),0)

I'd use an eval() function in other languages to create a meaning max cell range, but I'm lost how to do this in an excel function.

Can anyone give me some advice?

thanks!!

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### Jonmo1

##### MrExcel MVP
Not tested, but I think this will work...

=SUM(IF(\$A5=raw!\$O\$2:INDEX(raw!O:O,H2),raw!\$F\$2:INDEX(raw!F:F,H2)-raw!\$E\$2:INDEX(raw!E:E,H2),0),0)

Hope This Helps...

#### sbozicevic

##### New Member
Nice!

You people are more useful than soap in a shower!

#### Jonmo1

##### MrExcel MVP
You people are more useful than soap in a shower!

Remind me not to have you over for dinner... PE-EW !!!

#### sbozicevic

##### New Member
Ha!

I promise not to try to use you in the shower instead of soap... =/

Replies
3
Views
349
Replies
4
Views
1K
Replies
4
Views
373
Replies
5
Views
241
Replies
1
Views
246

1,191,183
Messages
5,985,172
Members
439,944
Latest member
Vangelis74

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back