+ Reply to Thread
Results 1 to 17 of 17

Count cells starting from blank and reset after another blank

  1. #1
    Registered User
    Join Date
    09-06-2013
    Location
    Winterville, NC, USA
    MS-Off Ver
    Excel 2013
    Posts
    23

    Count cells starting from blank and reset after another blank

    In the following example
    E F G H I J R
    3 y y y y y
    4 y y y y y
    5 y y y y
    if I am counting the number of consecutive "y" s the answer in cell R3 would be "3"
    but the answer in cell R4 would be "2"
    but the answer in cell R5 would be "1"

    Thanks in advance
    Last edited by slasherdan; 09-07-2013 at 12:06 AM.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Count cells starting from blank and reset after another blank

    E
    F
    G
    H
    I
    J
    K
    L
    3
    y y y y y
    =IFERROR(SUMPRODUCT((COLUMN(E3:J3)>LOOKUP(2,1/((E3:J3="")*(F3:K3="Y")),COLUMN(E3:J3)))*(E3:J3="Y")),COUNTIF(E3:J3,"Y"))
    4
    y y y y y
    =IFERROR(SUMPRODUCT((COLUMN(E4:J4)>LOOKUP(2,1/((E4:J4="")*(F4:K4="Y")),COLUMN(E4:J4)))*(E4:J4="Y")),COUNTIF(E4:J4,"Y"))
    5
    y y y y
    =IFERROR(SUMPRODUCT((COLUMN(E5:J5)>LOOKUP(2,1/((E5:J5="")*(F5:K5="Y")),COLUMN(E5:J5)))*(E5:J5="Y")),COUNTIF(E5:J5,"Y"))


    E
    F
    G
    H
    I
    J
    K
    L
    3
    y y y y y
    3
    4
    y y y y y
    2
    5
    y y y y
    1


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: Count cells starting from blank and reset after another blank

    Is it possible?


    E
    F
    G
    H
    I
    J
    6
    y
    y
    y
    y


    Two blank cells separated as F6 and I6 in the example above.

    If so what is the expected result?
    Marcelo Branco

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Count cells starting from blank and reset after another blank

    Quote Originally Posted by mlcb View Post
    If so what is the expected result?
    As per OP's statement in Post #1, I believe it should be 1

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Count cells starting from blank and reset after another blank

    @ slasherdan,

    Thanks for the rep

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

  6. #6
    Registered User
    Join Date
    09-06-2013
    Location
    Winterville, NC, USA
    MS-Off Ver
    Excel 2013
    Posts
    23

    Re: Count cells starting from blank and reset after another blank

    no problem that's the closest I have gotten in weeks, BUT it does not do completely right if I expand the cell ranges. I am now using this formula.
    =IFERROR(SUMPRODUCT((COLUMN(F4:Q4)>LOOKUP(2,1/((F4:Q4="")*(F4:Q4="Y")),COLUMN(F4:Q4)))*(F4:Q4="Y")),COUNTIF(F4:Q4,"Y")) but if I put "y" in a cell if continues to add all "y"s ignoring blanks.
    any updates?

  7. #7
    Registered User
    Join Date
    09-06-2013
    Location
    Winterville, NC, USA
    MS-Off Ver
    Excel 2013
    Posts
    23

    Re: Count cells starting from blank and reset after another blank

    mlcb

    it should be 1!
    Last edited by slasherdan; 09-07-2013 at 02:28 AM.

  8. #8
    Registered User
    Join Date
    06-30-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    70

    Re: Count cells starting from blank and reset after another blank

    if u want to count y after from first blank then this formula will work
    put this formula in L3 and press ctrl+shift enter

    =COUNTA(OFFSET(F3,0,MATCH(0,IF(ISBLANK(F3:K3),0,1),0)):K3)

    but if u hv this | y | | y | | y | y | and u want ans. 2 so this formula is not work it will give u 3 bcoz it starts counting from first blank

    hope this will help u

  9. #9
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Count cells starting from blank and reset after another blank

    Quote Originally Posted by slasherdan View Post
    but if I put "y" in a cell if continues to add all "y"s ignoring blanks.
    any updates?
    Please show it in excel for better understanding to check where it is failing...

  10. #10
    Registered User
    Join Date
    06-30-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    70

    Re: Count cells starting from blank and reset after another blank

    i rectify the formula, use this :

    =COUNTA(OFFSET(F3,0,IFERROR(MATCH(0,IF(ISBLANK(F3:K3),0,1),0),1)):K3)
    and press ctrl+shift enter

  11. #11
    Registered User
    Join Date
    09-06-2013
    Location
    Winterville, NC, USA
    MS-Off Ver
    Excel 2013
    Posts
    23

    Re: Count cells starting from blank and reset after another blank

    I have attached a sample both of the suggested codes are listed highlight to discover which one is yours and good luck troubleshooting.

    thanks again


    demo.xlsx

  12. #12
    Registered User
    Join Date
    09-06-2013
    Location
    Winterville, NC, USA
    MS-Off Ver
    Excel 2013
    Posts
    23

    Re: Count cells starting from blank and reset after another blank

    Quote Originally Posted by mlcb View Post
    Is it possible?


    E
    F
    G
    H
    I
    J
    6
    y
    y
    y
    y


    Two blank cells separated as F6 and I6 in the example above.

    If so what is the expected result?
    the answer would be (should be) 1

  13. #13
    Registered User
    Join Date
    09-06-2013
    Location
    Winterville, NC, USA
    MS-Off Ver
    Excel 2013
    Posts
    23

    Re: Count cells starting from blank and reset after another blank

    Quote Originally Posted by pwnyadav007 View Post
    i rectify the formula, use this :

    =COUNTA(OFFSET(F3,0,IFERROR(MATCH(0,IF(ISBLANK(F3:K3),0,1),0),1)):K3)
    and press ctrl+shift enter


    doesn't work right but your formula is in S3 in my sample. and yes it starts counting after first blank but does start counting after finding another blank.
    Thanks for trying

  14. #14
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Count cells starting from blank and reset after another blank

    You wrongly applied my formula....

    Right formula...
    =IFERROR(SUMPRODUCT((COLUMN(F3:Q3)>LOOKUP(2,1/((F3:Q3="")*(G3:R3="Y")),COLUMN(F3:Q3)))*(F3:Q3="Y")),COUNTIF(F3:Q3,"Y"))

    Check the reference of my previous suggestion too...

  15. #15
    Registered User
    Join Date
    09-06-2013
    Location
    Winterville, NC, USA
    MS-Off Ver
    Excel 2013
    Posts
    23

    Re: Count cells starting from blank and reset after another blank

    Quote Originally Posted by :) Sixthsense :) View Post
    Please show it in excel for better understanding to check where it is failing...
    Attachment 263563 your formula is the S3 cell answer should be 1.

  16. #16
    Registered User
    Join Date
    09-06-2013
    Location
    Winterville, NC, USA
    MS-Off Ver
    Excel 2013
    Posts
    23

    Re: Count cells starting from blank and reset after another blank

    typos...gotta love them! lol that fixed it

  17. #17
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Count cells starting from blank and reset after another blank

    Glad it helps you and thanks for the feedback

+ 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 52 Cells Starting From First Non Blank Cell In Range
    By Caedmonball19 in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 08-20-2013, 12:23 PM
  2. Replies: 4
    Last Post: 07-05-2012, 12:05 PM
  3. [SOLVED] Count Column blank entries, reset after entry in cell
    By Sedge in forum Excel General
    Replies: 4
    Last Post: 01-27-2011, 02:27 PM
  4. Code to count and then reset to 0 or blank out.
    By SVTF in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 04-12-2010, 08:53 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