One fix formula for variable set of cells reference

I have the following formula that is located in column O, with variable data entry in every set of different numbers of rows:
IF(OR(B1={"AAA","AAA1"}),IFERROR(IF((H2-G1)*2.417/24>SUM(M1+M2),(H2-G1)*2.417/24-SUM(M1+M2),""),""),"")

Is it possible to create a multiple non similar blocks for specific adjacent cells in a worksheet as the red border in the attached sheet and edit the above formula to refer to a specific cell’s location by column letter and the order of the cell location in that specific column for that particular block and apply the formula function?

Example reference to the attached worksheet:
The formula is based on cells H2, G1, M1 and M2 which is in line with the formula, is there a way to have the formula function as follows for the next set of data rows in the attached sheet without having to frequently change the formula for each set of rows data ?
IF(OR(B4={"AAA","AAA1"}),IFERROR(IF((H7-G4)*2.417/24>SUM(N4:N7),(H7-G4)*2.417/24-SUM(N4:N7),""),""),"")

Be kindly informed I would like please at the end to have one fixed formula in column O that deal with the variable numbers of block data rows in every worksheet of my workbook, is that possible?

The worksheet is protected.

Please unprotect before uploading.

Attached is unprotected version of book1

Would you also add the actual results you are expecting to get from a single formula so that we have a BEFORE and AFTER position so that we can check any answer

Refer please to the attached book3 the result in O1 cell is the correct expected result.

Be kindly informed the attached data is only a part of a longer sheet.

Without having to change the formula for every cells in column O in the original sheet, I expect cell O4 to return 2:13.
Notes please the formula in cell O4, subtract H5-G4 instead of subtracting H7-G4, in addition the present formula is adding M4+M5, instead of adding M4:M7.

I can edit the formula in O4 to return the expected time 2:13 but then I will have to do that for every block cell entry.

I need a way please for the one formula in column O that will account for the different number of rows entry.

This is still not clear, at least to me.

What determines the number of rows? Is that the next blank row below a series of cells.

Are the values in column A always AAA or BBB, I suspect not, with AAA always being the first in every block and only once in every block.
If not are there a mixture of many values.

Sorry for the late reply due to time zone difference.

You guessed it right the next blank row below a series of cells is what determines the number of rows.

The values in column A are not always AAA or BBB there are more chooses from the dropdown menu in column A, CCC was given as an example in the attached sheet, but you were right again either AAA or AAA1 are always at the being or first in every new block and only use once in every block, AAA1 was not listed in the sample dropdown menu but it is there in the original version of the worksheet.

AAA or AAA1 determine the beginning of each block, completely blank row indicates the end of the block.

Here's one solution.
There may be simpler variations.

Normally I'd use helper columns to simplify things, but here's everything in say O1 copied down

Formula:

Please Login or Register  to view this content.

Change the references to A7 to whatever your range is, or if you prefer just use a large number that will never be exceeded

Both OR and AND statements require a closing parentheses, hence

IF(OR(A1="AAA",A1=”AAA1”),IFERROR(....blah blah

..alternatively of course you could possibly avoid an OR by just looking at the first character.
i.e.

IF(LEFT(A1,1)="A"....etc...

I did the closing parentheses as follows, now the modified formula unfortunately returned #NAME? how that can be fixed please?

IF(OR(A1="AAA",A1=”AAA1”),IFERROR(IF((INDEX(H1:H7,IF(OR(A1="AAA",A1=”AAA1”),COUNTBLANK(A1:$A7)+1,""),1)-G1)*2.417/24>SUM(OFFSET(M1,0,0,IF(OR(A1="AAA",A1=”AAA1”),COUNTBLANK(A1:$A7)+1,""),1)),(INDEX(H1:H7,IF(OR(A1="AAA",A1=”AAA1”),COUNTBLANK(A1:$A7)+1,""),1)-G1)*2.417/24-SUM(OFFSET(M1,0,0,IF(OR(A1="AAA",A1=”AAA1”),COUNTBLANK(A1:$A7)+1,""),1)),""),""),"")

That second set of inverted commas in each section look wrong

IF(OR(A1="AAA",A1=”AAA1”)

try

IF(OR(A1="AAA",A1="AAA1")

After performing the last formula modification, I created a new block for testing purposes, that is after I did change the references to A11 and H11, unfortunately I lost all the expected times in the following cells:

0:46 in cell O1
2:13 in cell O4
4:09 in cell O9

Attached is the modified working sheet, Please refer to the modified formulas in column O for any modification that I may performed incorrectly.

OK,

Here's a modification. The original only worked when there were two blocks of data (implying only ever one blank row)

Formula:

Please Login or Register  to view this content.

Ensure the last cell in the ranges covered is at least one row below the last item. In this example your last row is 11, hence you see H1:H$12 and A1:A$12 in the formula. You could just make this the largest possible row ever likely to avoid the need to change it.

Applying the formula to the rest of my book worksheets, I came across a block off rows which has a multiple AAA entry in one block, the formula returned two times instead of one time! one time in cell O13 and the other one in cell O15! as it appear in the attached sample sheet, while the expected result should be one time for each block which is in this case (0:15) in cell O13.

I was wondering is there a way please to modify the last provided formula to look at the first AAA or AAA1 entry at the beginning of each block only and ignore the remaining AAA or AAA1 that exist in the same block of rows in order to avoid the incorrect second time result in cell O15 for the such a case of blocks ?

Hi,

I suspect this is a much simplified example and probably doesn't represent your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore upload your real workbook (or at least a cut down copy).

But let me pose a question first.
We are having to incorporate more functions as the complexity grows. The complexity disappears if the column A data can contain a unique occurrence in the first cell of the block.
From where do you get this data? If it's imported from another system is it not possible to go back to the source and ensure the first cell is unique. You only show columns A:O. Is there other data in the file you use since maybe that might help to identify the start of a block

A cut down copy of the actual sheet is attached, column A has an (OFF) input data only that is not related at all to the formula in column U. it has to do with pay credit only that is calculated in a different cell at the bottom of the working sheet.

...and what's the answer to where does the information come from?

I am sorry I did not get your question, what column or row data please you are referring to in your question?

If you referring to all data in general, I obtain it from my work logbook and input it manually in the working sheet.

If you input it manually then surely you can make the first cell in each block unique and the current formula will work.

Yes you are right, I can make the first cell in each block unique, the problem I am using the chooses from the dropdown list in column B to trigger other column formulas to work, in that case I will have to modify so many formulas.

Any idea please in how to get around that?

See the attached.

I've used a helper column A to identify the first cell in a block, and columns V & W to simplify the column U formula

Thanks,

Glad we got there in the end.
You may still find it useful and make it easier to understand if you break the single big formula down and use the two helper columns I showed in my last attachment