# Count 52 Cells Starting From First Non Blank Cell In Range

1. ## Count 52 Cells Starting From First Non Blank Cell In Range

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.

2. ## Re: Count 52 Cells Starting From First Non Blank Cell In Range

I have this formula to return the first non blank cell.

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

3. ## Re: Count 52 Cells Starting From First Non Blank Cell In Range

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

5. ## Re: Count 52 Cells Starting From First Non Blank Cell In Range

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?

6. ## Re: Count 52 Cells Starting From First Non Blank Cell In Range

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

7. ## Re: Count 52 Cells Starting From First Non Blank Cell In Range

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

8. ## Re: Count 52 Cells Starting From First Non Blank Cell In Range

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?

9. ## Re: Count 52 Cells Starting From First Non Blank Cell In Range

One issue seems to be the use of offset and cell functions. It seems to require the indirect function. So I changed it to:

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.

10. ## Re: Count 52 Cells Starting From First Non Blank Cell In Range

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

11. ## Re: Count 52 Cells Starting From First Non Blank Cell In Range

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

12. ## Re: Count 52 Cells Starting From First Non Blank Cell In Range

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

13. ## Re: Count 52 Cells Starting From First Non Blank Cell In Range

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

14. ## Re: Count 52 Cells Starting From First Non Blank Cell In Range

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

15. ## Re: Count 52 Cells Starting From First Non Blank Cell In Range

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.

16. ## Re: Count 52 Cells Starting From First Non Blank Cell In Range

Originally Posted by Caedmonball19
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

17. ## Re: Count 52 Cells Starting From First Non Blank Cell In Range

ChemistB that returns a N/A value for me.

18. ## Re: Count 52 Cells Starting From First Non Blank Cell In Range

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

19. ## Re: Count 52 Cells Starting From First Non Blank Cell In Range

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

20. ## Re: Count 52 Cells Starting From First Non Blank Cell In Range

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

21. ## Re: Count 52 Cells Starting From First Non Blank Cell In Range

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

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

22. ## Re: Count 52 Cells Starting From First Non Blank Cell In Range

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1