+ Reply to Thread
Results 1 to 3 of 3

Can OFFSET function be set up to ignore blank rows?

  1. #1
    Registered User
    Join Date
    12-08-2021
    Location
    Bath
    MS-Off Ver
    365
    Posts
    1

    Smile Can OFFSET function be set up to ignore blank rows?

    Good evening all

    I'm new here and not particularly confident with excel, so looking for some guidance!

    I'm looking for a formula which will sum 52 rows containing data from a specified point ie ignoring any blank cells

    I'm using the OFFSET function but it includes any blank cells

    Can the following formula be modified to only include cells with a value?

    =SUM(OFFSET(E5,0,0,52))

    Any help greatly appreciated

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Can OFFSET function be set up to ignore blank rows?

    As blanks are 0 it will make no difference to the SUM if they are included or ignored.
    Last edited by JohnTopley; 12-08-2021 at 05:28 PM.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Can OFFSET function be set up to ignore blank rows?

    With 365

    =E5:INDEX($E:$E,ROW(E5)+51) to list all values in the range
    =LET(x,E5:INDEX($E:$E,ROW(E5)+51),FILTER(x,x<>"")) to list non-blank values in the range
    =SUM(LET(x,E5:INDEX($E:$E,ROW(E5)+51),FILTER(x,x<>""))) to sum non-blank values in the range
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ 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. How do I: getting Offset to ignore blank cells
    By Learning ExL in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-27-2020, 10:39 PM
  2. [SOLVED] Ignore blank rows in an IF Statement
    By connieb in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-28-2019, 09:41 PM
  3. Ignore blank rows while printing
    By anilpatni1234 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-14-2018, 07:17 AM
  4. Index Match Function? Ignore blank rows - (rows without values)
    By jgray in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-26-2015, 01:12 PM
  5. Stdev / offset function to ignore #N/A
    By joshnathan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-27-2014, 08:44 AM
  6. [SOLVED] How can I ignore blank rows in this VB
    By Ginge1267 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 11-14-2013, 07:21 AM
  7. How can I ignore BLANK or ZERO cells in a MIN/IF function??
    By showmethemoney in forum Excel General
    Replies: 1
    Last Post: 02-02-2012, 11:46 PM

Tags for this Thread

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