isnumber search/match help

orsm6

Active Member
Joined
Oct 3, 2012
Messages
383
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Oct 3, 2012
Messages
383
Office Version
  1. 365
Platform
  1. Windows
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top