Index on Sum of Match

[8Ball963]

Good Morning,

I have a spreadsheet with multiple tabs of data that I need to sum 2 rows of data in 1 tab, then perform a less than operation on that sum, in order to output a date in a row where the balance in another row in less than that number. I have successfully performed the Index/Match on 1 line of data to report a date in row 59 when the balance in row 108 goes negative(the value in G4 is 0):

{=IFERROR(INDEX(INDIRECT("'"&$B7&"'!$BH$59:$FC$59")-4,MATCH(TRUE,INDIRECT("'"&$B7&"'!$BH$108:$FC$108")<$G$4,0)),"NO Tip Point")}.

What I need to do is sum rows 92 and 108, then perform a less than operation on that and report a date in line 59 where the balance falls below the less than operation of the sum. This is where I get either a #N/A, #REF, or #VALUE error. The syntax below gives me a #N/A:

{=INDEX(INDIRECT("'"&$B7&"'!$BH$59:$FC$59")-4,MATCH(SUM(INDIRECT("'"&$B7&"'!$BH$92:$FC$92"),INDIRECT("'"&$B7&"'!$BH$108:$FC$108"))<(AI7-AQ7),0))}

Thank you in advance for any help you can provide.

Bill

 

[jasonb75]

Best guess from 2 broken formulas

=INDEX(INDIRECT("'"&$B7&"'!$BH$59:$FC$59")-4,MATCH(TRUE,(INDIRECT("'"&$B7&"'!$BH$92:$FC$92")+INDIRECT("'"&$B7&"'!$BH$108:$FC$108"))<(AI7-AQ7),0))

Note that if any of the cells in row 59, 92, or 108 contain text (including formulas that show blank cells) then this will fail.