+ Reply to Thread
Results 1 to 14 of 14

How to count consecutive columns

  1. #1
    Registered User
    Join Date
    07-02-2010
    Location
    Birmingham
    MS-Off Ver
    Excel 2007
    Posts
    21

    How to count consecutive columns

    Dear all,

    Once again I turn to the excellent forum for help with a simple excel problem.

    I have a worksheet in excel 2007. The sheet has several hundred rows, and about 100 columns.
    For each row there are many blank entries.

    i need to know for each row, how many consecutive columns contain data.

    I'm sure this is easy, but I can't think of how to do it simply.

    I've attached a dummy, simplified worksheet to illustrate the problem.

    Thanks,
    A
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    05-10-2012
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: How to count consecutive columns

    Hi,

    In cell N2, write the following:

    =COUNT(B2:M2)

    and paste down.

    Thank you.

    Kind Regards,
    theabdulrab.

  3. #3
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: How to count consecutive columns

    Maybe this: =SUMPRODUCT(--(B2:M2<>""),--(A2:L2<>""))-(B2<>"")

  4. #4
    Registered User
    Join Date
    07-02-2010
    Location
    Birmingham
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: How to count consecutive columns

    Thanks for the quick replies. The sumproduct formula didn't work. It got it right in some rows, but not in others...

  5. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: How to count consecutive columns

    Can you tell us in what did work (or didn't) or we need to guess

  6. #6
    Registered User
    Join Date
    07-02-2010
    Location
    Birmingham
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: How to count consecutive columns

    oops, sorry.
    If you apply the formula, the first row value is 5, but there are only a maximum of 3 consecutive columns containing data in that row.

    Thanks,
    A

  7. #7
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: How to count consecutive columns

    Check now...

    Notice that I need to add two columns B and O (and then I left them empty hide them)
    Attached Files Attached Files

  8. #8
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How to count consecutive columns

    Hi

    Is this Array(Control+Shift+Enter) formula, works for you?

    =MAX(FREQUENCY(IF(B2:M2>=1,COLUMN(B2:M2)),IF(B2:M2<1,COLUMN(B2:M2))))
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  9. #9
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: How to count consecutive columns

    It give slightly me different results at the end... Hope at least one solution is good

  10. #10
    Registered User
    Join Date
    07-02-2010
    Location
    Birmingham
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: How to count consecutive columns

    Thanks for the quick response.

    This works for the first row, but again there is a problem further down.

    For example, look at the last row: it gives a value of 2, but there are 4 consecutive columns with data in that row.

    Sorry, this is a tricky problem.

    Thanks again,
    A

  11. #11
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: How to count consecutive columns

    Then Fotis solution will work for you.

    I understood this as there are 2 consecutive columns in last row: 1,1 and 1,4,5,1 as 2 pairs of consecutive columns.

  12. #12
    Registered User
    Join Date
    05-10-2012
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: How to count consecutive columns

    dummy sheet.xlsx

    Hi,

    Does the attached file serve the purpose?

    Thank you.

    Kind Regards,
    theabdulrab.

  13. #13
    Registered User
    Join Date
    07-02-2010
    Location
    Birmingham
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: How to count consecutive columns

    Thanks all. Fotis' solution worked perfectly for me.

    What an excellent forum!

    A

  14. #14
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How to count consecutive columns

    Glad that you found your solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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