Total Empty column between two characters
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Total Empty column between two characters

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

    Default Total Empty column between two characters

    Hello Team,

    I need your help with a formula to calculate how many empty column between of the letter “A” as in the row#2. The result that I am looking for should be the same as in row#1 as shown below.

    Any help would be greatly appreciated.
    BDT


    A B C D F G H I J K L M N O P Q R S T U V W X Y Z AA
    ROW#1 1 2 1 7 2
    ROW#2 A A A A A A A A A A A

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,070
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Total Empty column between two characters

    in A1
    =IF(AND(A2="",B2="A"),COLUMN()-MAX(IF($A2:A2="A",COLUMN(($A2:A2)))),"")
    Array formula, use Ctrl-Shift-Enter

    copy along as far as AA1

  3. #3
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Total Empty column between two characters

    Hi,

    Below is same fundamental logic as Special-K99 but without invoking Ctrl+Shift+Enter:

    Code:
    =IF(AND(C2="A",B2=""),COLUMN()-AGGREGATE(14,6,($A2:B2<>"")*COLUMN($A2:B2),1),"")
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  4. #4
    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

    Hi,

    Here's another way:

    ABCDEFGHIJKLM
    1121
    2AAAAAAAAA

    Sheet688



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



    A1 formula copied across.
    Last edited by jtakw; Jul 15th, 2019 at 12:15 PM.

  5. #5
    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

    Special-K99,

    I tested your formula and it worked.

    Thank you so much!
    BDT

  6. #6
    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

    Aryatect,

    I used for your formula and got the result I am looking for.

    Thanks much for your help.
    BDT

  7. #7
    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

    Quote Originally Posted by jtakw View Post
    Hi,

    Here's another way:

    A B C D E F G H I J K L M
    1 1 2 1
    2 A A A A A A A A A
    Sheet688

    Worksheet Formulas
    Cell Formula
    A1 =IF(AND(A2="",B2="A"),COLUMN()-IFERROR(LOOKUP("A",$A2:A2,COLUMN($A2:A2)),0),"")



    A1 formula copied across.

    JTAKW,

    I tested your formula and somehow I got the unexpected result as below. Not sure it was on my end error.

    I copied to the A1 as instructed.

    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 1 5 7 10 7
    A A A A A A A A A A A A A A

    I should get

    S1 = 1
    V1 = 2
    Y1 = 2

    Thanks,
    BDT

  8. #8
    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

    Not sure why, but just swap out the "A" with "zzz" like below:

    ABCDEFGHIJKLMNOPQRSTUVWXYZ
    1115122
    2AAAAAAAAAAAAAA

    Sheet688



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



    A1 formula copied across.

  9. #9
    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

    Here is another normally entered solution...

    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="",B2="A"),1,"")

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

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

  10. #10
    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

    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

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
  •