Count 52 Cells Starting From First Non Blank Cell In Range

[Caedmonball19]

I am trying to take an average of hours worked in a persons first 12 months of employment. With the sheet starting with our fiscal year not everyone starts in the first pay period which is why I need it to start in the first non blank cell. I have the sheet spanning 2 years to cover those that start late in the first fiscal year.

I hope this is clear enough I am not sure if this is an OFFSET formula but not sure how to identify the first non blank cell. Thanks.

[Caedmonball19]

I have this formula to return the first non blank cell.
=CELL("address",INDEX(F7:FL7,MATCH(TRUE,F7:FL7>0,0)))

Now I am looking for a way to count the first 52 cells in a range. Any ideas?

[L-Drr]

the SUM formula for an adjecent range of cells looks like this: =SUM(first cell:last cell)
so if you use your formula for the first cell and your formula combined with OFFSET of 52 rows for the last cell, you should get the right range

[L-Drr]

So: =SUM((CELL("address",INDEX(F7:FL7,MATCH(TRUE,F7:FL7>0,0)))):(OFFSET(CELL("address",INDEX(F7:FL7,MATCH(TRUE,F7:FL7>0,0))),51,0)))

[Caedmonball19]

Would this all need to be contained within one formula? If so it may be outside of my knowledge base. If not then how do I use the cell reference from my previous formula within the new formula?

[Caedmonball19]

Nevermind I am just seeing your second post. I will test it out. Thanks.

[Caedmonball19]

Excel does not approve of that formula. It spits out a "We found a problem" window.

[L-Drr]

Then I think there is something going wrong in the combination of your formula with my SUM and OFFSET. Could you post an example file in which your formula works?

[Caedmonball19]

One issue seems to be the use of offset and cell functions. It seems to require the indirect function. So I changed it to:
=SUM((CELL("address",INDEX(F10:BC10,MATCH(TRUE,F10:BC10>0,0)))): (OFFSET(INDIRECT(CELL("address",INDEX(F10:BC10,MATCH(TRUE,F10:BC10>0,0)))),0,26,1,1)))

If I separate the formula into two and try the two parts separately they seem to work but don't like being together. And yes I did change to 26 pay periods instead of 52 weeks.

[Caedmonball19]

PayrollHoursHelp2.xlsx
Here is the file, it contains all dummy information. The column I am looking at for this formula is D.

[L-Drr]

Hmm, strange. Your formula in post #2 does return cell coordinates (like "B5"), right?

Once again, if you could post your workbook, it would make the troubleshooting a lot easier for me.

Could it be syntax? I know I use ; instead of your , so maybe the : in the SUM function should be something else for you?

Edit: just saw you posted the file

[Caedmonball19]

The file is in the above post. Thanks L-Drr

[L-Drr]

Could you put the = before your formula in D10 and upload again? Then I don't have to translate every word in your formula

[Caedmonball19]

It won't let me since it doesn't like the formula.

[ChemistB]

Not sure about your file but to get the sum for 52 cells starting in the first nonblank cell

=SUM(OFFSET(INDEX($F7:$FL7,MATCH(1,$F7:$FL7)),0,0,1,52))
Does that work for you?

EDIT: Nevermind, that doesn't work. Back to the drawing board.

[L-Drr]

It won't let me since it doesn't like the formula.

Sorry of course. The problem seems to be in your formula to find the first non-blank cell; I can't get that to work

[Caedmonball19]

ChemistB that returns a N/A value for me.

[ChemistB]

This formula entered as an array (use CNTRL SHFT ENTER instead of Enter. If done properly, you'll see {} around formula)

=SUM(OFFSET(INDEX($F$7:$FL$7,MATCH(1,1/($F$7:$FL$7<>0),0)),0,0,1,52))

[ChemistB]

I put that formula in E7 and copied down in your example spreadsheet.

[Caedmonball19]

ChemistB you are a genius, a much simpler solution to my issue. Thank You.

[Pete_UK]

If you want to avoid volatile functions, put this array* formula in E7:

Please Login or Register  to view this content.

and then copy down to E10.

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual <Enter>.

Hope this helps.

Pete

[Pete_UK]

As you have two-weekly pay periods, you need to change the 52 in ChemistB's formula to 26, then it gives the same results as mine.

Hope this helps.

Pete