+ Reply to Thread
Results 1 to 9 of 9

sum of consecutive counts without blanks

  1. #1
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    Excel 2010
    Posts
    2,428

    sum of consecutive counts without blanks

    50
    50
    40


    10


    I have the above on , a1 to a6 , i need a formula to count and give me result 3

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: sum of consecutive counts without blanks

    Try this:

    =MAX(FREQUENCY(IF(A1:A6<>"",ROW(A1:A6)),IF(A1:A6="",ROW(A1:A6)))) Ctrl Shift Enter
    Last edited by 63falcondude; 05-23-2017 at 02:42 PM.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,011

    Re: sum of consecutive counts without blanks

    Use this array formula:

    =MIN(IF(A1:A6="",ROW(A1:A6)-ROW(A1)+1))-1

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Glenn



  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,011

    Re: sum of consecutive counts without blanks

    Ignore my formula: it breaks down!! use 63 falcon..... 's one instead!!

  5. #5
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    Excel 2010
    Posts
    2,428

    Re: sum of consecutive counts without blanks

    Thank you 63 falcon dude , it sorted me out
    Last edited by makinmomb; 05-23-2017 at 02:59 PM.

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: sum of consecutive counts without blanks

    Quote Originally Posted by makinmomb View Post
    Thank you 63 falcon dude , sort me out
    You're welcome. Glad to help.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,011

    Re: sum of consecutive counts without blanks

    Falcon's doesn't work, either, You need this array formula:

    =IF(MIN(IF(A1:A6="",ROW(A1:A6)-ROW(A1)+1))=0,ROW(A6),MIN(IF(A1:A6="",ROW(A1:A6)-ROW(A1)+1))-1)

    On the attached sheet, delete the value in either A2 or A3...
    Attached Files Attached Files

  8. #8
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: sum of consecutive counts without blanks

    Quote Originally Posted by Glenn Kennedy View Post
    Falcon's doesn't work, either, You need this array formula:

    =IF(MIN(IF(A1:A6="",ROW(A1:A6)-ROW(A1)+1))=0,ROW(A6),MIN(IF(A1:A6="",ROW(A1:A6)-ROW(A1)+1))-1)

    On the attached sheet, delete the value in either A2 or A3...
    The formula suggested in post #2 returns the maximum number of consecutive non-blank cells. I am assuming that is what the OP wanted.

    If the OP was looking for the number of consecutive non-blank cells from the top of the column, then you are correct in saying that my formula will not do that.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,011

    Re: sum of consecutive counts without blanks

    Agreed, but I'll bet that he'd want 1, if a2 is blank. We'll see.

+ 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. Sum after first three consecutive blanks
    By hjac1217 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-21-2017, 11:50 AM
  2. [SOLVED] Consecutive numbers ignoring blanks
    By pdauction in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-30-2016, 07:50 AM
  3. [SOLVED] Counts two consecutive number???
    By thangkhi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-13-2013, 01:07 PM
  4. IF greater than negative value counts blanks as zero
    By fransden in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-15-2013, 03:03 PM
  5. Countif function counts blanks
    By ckenn134 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-27-2013, 03:31 PM
  6. [SOLVED] Count consecutive non-zero values; list first non-zero value; display counts as an array
    By treznick in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-17-2012, 08:25 PM
  7. Consecutive Counts
    By Jarbugs in forum Excel General
    Replies: 3
    Last Post: 01-19-2009, 09:57 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