isnumber search/match help

[orsm6]

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

 

[KRice]

Give this a try...it's messy:

Book2
	E				I
5	18
6
7	Text				Value
8	a				1
9	b				2
10	Bosch Startup				3
11	a				4
12	b				5
13	c				6
14	Scholle Startup				7
15	a				8
16	b				9
17	Bosch Startup				10
18	c				11
19	d				12
20	Scholle Startup				13
Sheet2

Cell Formulas
Range	Formula
E5	E5	=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]

Give this a try...it's messy:

Book2
	E				I
5	18
6
7	Text				Value
8	a				1
9	b				2
10	Bosch Startup				3
11	a				4
12	b				5
13	c				6
14	Scholle Startup				7
15	a				8
16	b				9
17	Bosch Startup				10
18	c				11
19	d				12
20	Scholle Startup				13
Sheet2

Cell Formulas
Range	Formula
E5	E5	=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