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