# Formula request

#### mac_see

##### Active Member
Let me put the scenerio across
The value of A1 is 1
The value of B1 is 2
The value of C1 is 3
The value of D1 is 4
The value of E1 is 5

The value of A2 is 100
The value of B2 is 200
The value of C2 is 300
The value of D2 is 400
The value of E2 is 500

The value of G1 is Row1
The value of G2 is Row2

H1 and H2 accepts value from the user.

If i have to put the value for
H1 to 2 and H2 to 4, the H3 cell should sum up the corresponding value from 2 to 4(B1:D1) that is 200+300+400=800

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### Mark W.

##### MrExcel MVP
I believe the result would be 900...
Book1
ABCDEFGHIJKL
1123452
21002003004005004
3900
4
5
6
Sheet5

##### MrExcel MVP
A non-volatile approach...

=SUM(INDEX(\$A\$1:\$E\$2,2,H1):INDEX(\$A\$1:\$E\$2,2,H2))

#### mac_see

##### Active Member
That was a terrific reply within a short span of time. One more question, if we go to sum it up reverse (for example the value of G1 is more than the value of G2) an error box should appear instead of #REF!.

Thanx
Maxi

#### mac_see

##### Active Member
Aladin Akyurek, don't wanna disappoint u but the one provided by the master looks good. Still I appreciate your efforts.

##### MrExcel MVP
On 2002-10-16 12:27, mac_see wrote:
That was a terrific reply within a short span of time. One more question, if we go to sum it up reverse (for example the value of G1 is more than the value of G2) an error box should appear instead of #REF!.

Thanx
Maxi

The INDEX version would still compute a sum, as it were, in reverse order. An "error box" would require VBA...

#### mac_see

##### Active Member
#REF! appears if I put 5 in G1 and 4 in G2. Another strange thing is, If I keep a blank value in any of the cells (G1 or G2), it should bring up a error box (By error box, I mean, the text that we type in the Validation option under Data menu)

#### Mark W.

##### MrExcel MVP
>#REF! appears if I put 5 in G1 and 4 in G2.

Of course, semantically G1 should be the 1st column -- not the 2nd. You're original post didn't indicate otherwise.

>Another strange thing is, If I keep a blank
>value in any of the cells (G1 or G2), it
>should bring up a error box (By error box,
>I mean, the text that we type in the

Change your Validation so it doesn't permit blanks by unchecking the "Ignore blank" option.

##### MrExcel MVP
On 2002-10-16 12:47, mac_see wrote:
#REF! appears if I put 5 in G1 and 4 in G2. Another strange thing is, If I keep a blank value in any of the cells (G1 or G2), it should bring up a error box (By error box, I mean, the text that we type in the Validation option under Data menu)

Although data validation can be defeated, you could try...

For H1...

Set Allow to: Whole Number

Choose Between

Set Minimum to: 1

Set Maximum to: =COLUMN(E1)

For H2...

Set Allow to: Custom

Set Formula to: =(H2>=H1)*(H2-INT(H2)=0)*(H2>0)*(H2<=COLUMN(E1))

I already suggested what to use in H3...
This message was edited by aladin akyurek on 2002-10-17 02:20

#### Mark W.

##### MrExcel MVP
If you want to enter values in G1:G2 in any order consider using...
Book1
ABCDEFGHIJKL
1123455
21002003004005004
3900
4
5
6
Sheet5

Replies
3
Views
284
Replies
3
Views
319
Replies
0
Views
298
Replies
7
Views
378
Replies
5
Views
629

1,181,236
Messages
5,928,824
Members
436,630
Latest member
sim981

### 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