# isnumber search/match help

#### orsm6

##### Active Member
Hi all - i am trying to modify my formula to somehow search my range for text - then from same row do some sums until it finds a row containing another text. I thought I had it working, but i seem to not be getting it. This is for our production schedule, and it is possible that either machine Bosch will start first or Scholle will start first.

can anyone help with a formula.... similar to an IF formula that will:
- if the text Bosch Startup is found first in column E8:E139 sum the contents of column I8:I139.... but only until the row BEFORE it finds text Scholle Startup
- then if the text Scholle Startup is found first in column E8:E139 sum the contents of column I8:I139.... but only until the row BEFORE it finds text Bosch Startup

If either statement doesn't find the second.... e.g. if Bosch comes first, but there was no Scholle startup, would it still just sum the whole column?

i tried to play with this formula, but it isn't working in all scenarios for me: =IF(ISNUMBER(MATCH("Bosch Startup",E8:E139,0)),SUM(I8:INDEX(I8:I139,MATCH("Scholle Startup",E8:E139,0)-1))+H140,SUM(I8:I139)-SUM(I141-H141)+H140)

i tried to swap things around within the formula too, but not working.

TIA

To edit this.... my original formula was: IF(E9="Bosch Startup",SUM(I8:INDEX(I8:I139,MATCH("Scholle Startup",E8:E139,0)-1))+H140,SUM(I8:INDEX(I8:I139,MATCH("Bosch Startup",E8:E139,0)-1))+H140) and it worked great BUT.... the text could be found anywhere in E8:E139... that is why i need to serach that range for the text.

thanks again

Last edited by a moderator:

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### KRice

##### Well-known Member
Give this a try...it's messy:
Book2
EI
518
6
7TextValue
8a1
9b2
10Bosch Startup3
11a4
12b5
13c6
14Scholle Startup7
15a8
16b9
17Bosch Startup10
18c11
19d12
20Scholle Startup13
Sheet2
Cell Formulas
RangeFormula
E5E5=IFERROR(IF(AND(MATCH("Bosch Startup",\$E\$8:\$E\$139,0),MATCH("Scholle Startup",\$E\$8:\$E\$139,0)), IF(MATCH("Bosch Startup",\$E\$8:\$E\$139,0)<MATCH("Scholle Startup",\$E\$8:\$E\$139,0), SUM(OFFSET(INDEX(\$I\$8:\$I\$139,MATCH("Bosch Startup",\$E\$8:\$E\$139,0)),0,0,MATCH("Scholle Startup",\$E\$8:\$E\$139,0)-MATCH("Bosch Startup",\$E\$8:\$E\$139,0))), SUM(OFFSET(INDEX(\$I\$8:\$I\$139,MATCH("Scholle Startup",\$E\$8:\$E\$139,0)),0,0,MATCH("Bosch Startup",\$E\$8:\$E\$139,0)-MATCH("Scholle Startup",\$E\$8:\$E\$139,0))))),"Not found")

#### orsm6

##### Active Member
Give this a try...it's messy:
Book2
EI
518
6
7TextValue
8a1
9b2
10Bosch Startup3
11a4
12b5
13c6
14Scholle Startup7
15a8
16b9
17Bosch Startup10
18c11
19d12
20Scholle Startup13
Sheet2
Cell Formulas
RangeFormula
E5E5=IFERROR(IF(AND(MATCH("Bosch Startup",\$E\$8:\$E\$139,0),MATCH("Scholle Startup",\$E\$8:\$E\$139,0)), IF(MATCH("Bosch Startup",\$E\$8:\$E\$139,0)<MATCH("Scholle Startup",\$E\$8:\$E\$139,0), SUM(OFFSET(INDEX(\$I\$8:\$I\$139,MATCH("Bosch Startup",\$E\$8:\$E\$139,0)),0,0,MATCH("Scholle Startup",\$E\$8:\$E\$139,0)-MATCH("Bosch Startup",\$E\$8:\$E\$139,0))), SUM(OFFSET(INDEX(\$I\$8:\$I\$139,MATCH("Scholle Startup",\$E\$8:\$E\$139,0)),0,0,MATCH("Bosch Startup",\$E\$8:\$E\$139,0)-MATCH("Scholle Startup",\$E\$8:\$E\$139,0))))),"Not found")
thank you for helping.... have had a quick play and so far it seems to be working, i will test it more tomorrow

Replies
2
Views
81
Replies
2
Views
259
Replies
3
Views
89
Replies
10
Views
405
Replies
3
Views
61

1,130,219
Messages
5,640,954
Members
417,182
Latest member
mgcorreia

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