+ Reply to Thread
Results 1 to 7 of 7

Check to see if nth cell is blank

  1. #1
    Registered User
    Join Date
    07-27-2021
    Location
    Europe
    MS-Off Ver
    O365
    Posts
    2

    Question Check to see if nth cell is blank

    Hi,

    I have a column of data and I want to check every 3rd cell i.e. A3, A6, A9 etc to see if they are blank and then return the number of blank cells. I do not care if A4, A5, A7, A8 is blank.

    Any help would be greatly appreciated!
    Last edited by Baccawastaken; 07-28-2021 at 05:47 PM.

  2. #2
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    365 & WPS(2022)
    Posts
    2,030

    Re: Check to see if nth cell is blank

    Try this non Array formula

    HTML Code: 
    =TEXT(SUM(COUNTIF(OFFSET(A$3,{0;3;6},,,),"")),"0;;;")
    Array formula
    HTML Code: 
    =SUM(COUNTBLANK(OFFSET(A$3,{0,1,2}*3,)))
    Last edited by wk9128; 07-28-2021 at 08:46 AM.

  3. #3
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    1,835

    Re: Check to see if nth cell is blank

    While having a location of Mars and a version of "Nunya" might be cute, it is important to know that information in order to give you a good answer. Local may alter a formula, and depending on what version you have, some formulas may not work. Please supply this info so we can better answer your question.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,497

    Re: Check to see if nth cell is blank

    The sum of the blank cells will be zero, so what’s the point? Or did you not state your requirements correctly?

    Administrative Note:

    Welcome to the forum.

    Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

    With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

    Also update your MS Office version, as this will affect potential solutions offered.

    Thank you for helping us to help you.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  5. #5
    Registered User
    Join Date
    07-27-2021
    Location
    Europe
    MS-Off Ver
    O365
    Posts
    2

    Re: Check to see if nth cell is blank

    I've updated my details but have found the solution myself.

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,663

    Re: Check to see if nth cell is blank

    Please share your solution. Another with the same question may find it helpful.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Dave

  7. #7
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,994

    Re: Check to see if nth cell is blank

    A way is =SUMPRODUCT((A1:A12="")*(MOD(ROW(A1:A12),3)=0))

    but your solution would be interesting

+ 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. [SOLVED] Check if any cell in range is blank
    By Undo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-15-2019, 03:34 PM
  2. [SOLVED] i want formula check cell if cell is blank go to next cell and check value and give grades
    By pksrikanth85 in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 06-19-2017, 12:42 PM
  3. [SOLVED] check if cell is blank or start with**
    By suske04 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-09-2017, 06:26 AM
  4. how to check if a cell is blank then run another formula
    By Parky63 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-07-2016, 11:56 AM
  5. Check if Blank, If so, Check the Next Cell
    By Watterboy in forum Excel General
    Replies: 5
    Last Post: 12-08-2011, 01:39 PM
  6. Check if any cell is blank.
    By spqr in forum Excel General
    Replies: 5
    Last Post: 06-18-2009, 04:29 AM
  7. Macro to check for blank cell entry, copy previous value, and check for duplicates
    By xPunxNotDeadx in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-26-2009, 06:33 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