Total Empty column between two characters
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: Total Empty column between two characters

  1. #11
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,097
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Total Empty column between two characters

    Quote Originally Posted by bdtran View Post
    All,

    While checking the data, I encountered a sheet containing the sample data as in the table below. Similarly, I need a formula for the result in row#1 with total of columns (not empty) between the letter "A".

    Thank you so much for all the help.
    BDT


    A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
    1 2 1 2 8
    2 A 1 0 A A A 0 A A A A A 1 1 A 1 1 0 1 0 1 1 0 A
    I think this modification to the formulas I posted in Message #9 will do what you are asking for...

    If it is possible for cell A2 to be blank, then put this formula in cell A1, otherwise leave cell A1 blank...

    =IF(AND(A2<>"A",B2="A"),1,"")

    Now put his formula in cell B1 and copy it across to the end of your data...

    =IF(AND(B2<>"A",C2="A"),COLUMNS($A1:B1)-COUNTIF($A2:B2,"A")-SUM($A1:A1),"")
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  2. #12
    New Member
    Join Date
    Oct 2018
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Total Empty column between two characters

    Mr. Rothstein,

    I tested both of your solutions for total empty and non-empty columns. I got the exactly result I am looking for.

    Thank you so much for your help on this situation.

    BDT

  3. #13
    New Member
    Join Date
    Oct 2018
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Total Empty column between two characters

    Jtakw,

    I replaced "A" with "zzz" as mentioned and it returned the corrected result.

    Thanks again with your help.
    BDT

  4. #14
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Total Empty column between two characters

    Here's the modified version my formula from Post # 8 that will handle both versions of your data in Post # 1 and # 10, whether or not the data starts with Blanks or Numbers in A2:

    ABCDEFGHIJKLMNOPQRSTUVWX
    12128
    2A10AAA0AAAAA11A11010110A

    Sheet688 (2)



    Worksheet Formulas
    CellFormula
    A1=IF(AND(A2<>"A",B2="A"),COLUMN()-IFERROR(LOOKUP("zzz",$A2:A2,COLUMN($A2:A2)),0),"")



    Formula copied across.

  5. #15
    New Member
    Join Date
    Oct 2018
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Total Empty column between two characters

    Thanks Jtakw,

    I tested your formula and it worked with expected result.

  6. #16
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Total Empty column between two characters

    You're welcome.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •