# Thread: Total Empty column between two characters Thanks: 0 Likes:  2 Post #5309613 (1)Post #5309635 (1)

1. ## Re: Total Empty column between two characters

Originally Posted by bdtran
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),"")

2. ## 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. ## 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. ## 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. ## Re: Total Empty column between two characters

Thanks Jtakw,

I tested your formula and it worked with expected result.

6. ## Re: Total Empty column between two characters

You're welcome.

## User Tag List

#### Posting Permissions

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