+ Reply to Thread
Results 1 to 22 of 22

Count 52 Cells Starting From First Non Blank Cell In Range

  1. #1
    Registered User
    Join Date
    05-04-2012
    Location
    Nebraska
    MS-Off Ver
    Excel 2003
    Posts
    70

    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. #2
    Registered User
    Join Date
    05-04-2012
    Location
    Nebraska
    MS-Off Ver
    Excel 2003
    Posts
    70

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

    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?

  3. #3
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    493

    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
    When I say semicolon, u say comma!

  4. #4
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    493

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

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

  5. #5
    Registered User
    Join Date
    05-04-2012
    Location
    Nebraska
    MS-Off Ver
    Excel 2003
    Posts
    70

    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. #6
    Registered User
    Join Date
    05-04-2012
    Location
    Nebraska
    MS-Off Ver
    Excel 2003
    Posts
    70

    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. #7
    Registered User
    Join Date
    05-04-2012
    Location
    Nebraska
    MS-Off Ver
    Excel 2003
    Posts
    70

    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. #8
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    493

    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. #9
    Registered User
    Join Date
    05-04-2012
    Location
    Nebraska
    MS-Off Ver
    Excel 2003
    Posts
    70

    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:
    =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.

  10. #10
    Registered User
    Join Date
    05-04-2012
    Location
    Nebraska
    MS-Off Ver
    Excel 2003
    Posts
    70

    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. #11
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    493

    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. #12
    Registered User
    Join Date
    05-04-2012
    Location
    Nebraska
    MS-Off Ver
    Excel 2003
    Posts
    70

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

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

  13. #13
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    493

    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. #14
    Registered User
    Join Date
    05-04-2012
    Location
    Nebraska
    MS-Off Ver
    Excel 2003
    Posts
    70

    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. #15
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,580

    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.
    Last edited by ChemistB; 08-20-2013 at 11:49 AM.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  16. #16
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    493

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

    Quote Originally Posted by Caedmonball19 View Post
    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. #17
    Registered User
    Join Date
    05-04-2012
    Location
    Nebraska
    MS-Off Ver
    Excel 2003
    Posts
    70

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

    ChemistB that returns a N/A value for me.

  18. #18
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,580

    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. #19
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,580

    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.
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    05-04-2012
    Location
    Nebraska
    MS-Off Ver
    Excel 2003
    Posts
    70

    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. #21
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,509

    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: copy to clipboard
    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
    Last edited by Pete_UK; 08-20-2013 at 12:23 PM.

  22. #22
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,509

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Count Non-Blank cells in range for each row
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-07-2012, 06:26 AM
  2. Count number of non-blank cells in a range
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-15-2011, 10:15 PM
  3. Replies: 9
    Last Post: 10-12-2010, 12:37 PM
  4. VBA code to count non blank cells in a range
    By James_B in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-13-2010, 02:19 PM
  5. Count blank cells within a range not including fully blank rows
    By twofootgiant in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 07-16-2008, 09:43 AM

Bookmarks

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