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

1. ## 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. ## 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. ## 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),"")`

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

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

Special-K99,

I tested your formula and it worked.

Thank you so much!
BDT

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

Aryatect,

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

BDT

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

Originally Posted by jtakw
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. ## 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. ## 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),"")

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