# Total Empty column between two characters

#### bdtran

##### New Member
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

<tbody>
</tbody>

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### Special-K99

##### Well-known Member
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

#### Aryatect

##### Active Member
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),"")``

#### jtakw

##### Well-known Member
Hi,

Here's another way:

A1 formula copied across.

Last edited:

#### bdtran

##### New Member
Special-K99,

I tested your formula and it worked.

Thank you so much!
BDT

#### bdtran

##### New Member
Aryatect,

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

BDT

#### bdtran

##### New Member
Hi,

Here's another way:

ABCDEFGHIJKLM
1121
2AAAAAAAAA

<tbody>
</tbody>
Sheet688

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

<tbody>
</tbody>

<tbody>
</tbody>

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

<tbody>
</tbody>

I should get

S1 = 1
V1 = 2
Y1 = 2

Thanks,
BDT

#### jtakw

##### Well-known Member
Not sure why, but just swap out the "A" with "zzz" like below:

A1 formula copied across.

#### Rick Rothstein

##### MrExcel MVP
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),"")

#### bdtran

##### New Member
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

<tbody>
</tbody>