Total Empty column between two characters

bdtran

New Member
Joined
Oct 11, 2018
Messages
33
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


ABCDFGHIJKLMNOPQRSTUVWXYZAA
ROW#112172
ROW#2AAAAAAAAAAA

<tbody>
</tbody>
 

Some videos you may like

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
Joined
Nov 7, 2006
Messages
8,342
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
Joined
Jun 17, 2019
Messages
308
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
Joined
Jun 29, 2014
Messages
5,146
Hi,

Here's another way:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";"></td><td style="text-align: right;;">1</td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">2</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">1</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">A</td><td style="text-align: right;;"></td><td style=";">A</td><td style=";">A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">A</td><td style=";">A</td><td style=";">A</td><td style=";">A</td><td style=";">A</td><td style="text-align: right;;"></td><td style=";">A</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet688</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A1</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">A2="",B2="A"</font>),COLUMN(<font color="Red"></font>)-IFERROR(<font color="Red">LOOKUP(<font color="Green">"A",$A2:A2,COLUMN(<font color="Purple">$A2:A2</font>)</font>),0</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

A1 formula copied across.
 
Last edited:

bdtran

New Member
Joined
Oct 11, 2018
Messages
33
Special-K99,

I tested your formula and it worked.

Thank you so much!
BDT
 

bdtran

New Member
Joined
Oct 11, 2018
Messages
33
Aryatect,

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

Thanks much for your help.
BDT
 

bdtran

New Member
Joined
Oct 11, 2018
Messages
33
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.

ABCDEFGHIJKLMNOPQRSTUVWXYZ
1157107
AAAAAAAAAAAAAA

<tbody>
</tbody>

I should get

S1 = 1
V1 = 2
Y1 = 2

Thanks,
BDT
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Not sure why, but just swap out the "A" with "zzz" like below:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";"></td><td style="text-align: right;;">1</td><td style=";"></td><td style=";"></td><td style="text-align: right;;">1</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">5</td><td style=";"></td><td style="text-align: right;;">1</td><td style=";"></td><td style=";"></td><td style="text-align: right;;">2</td><td style=";"></td><td style=";"></td><td style="text-align: right;;">2</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">A</td><td style="text-align: right;;"></td><td style=";">A</td><td style=";">A</td><td style="text-align: right;;"></td><td style=";">A</td><td style=";">A</td><td style=";">A</td><td style=";">A</td><td style=";">A</td><td style=";">A</td><td style=";">A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">A</td><td style="text-align: right;;"></td><td style=";">A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">A</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet688</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A1</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">A2="",B2="A"</font>),COLUMN(<font color="Red"></font>)-IFERROR(<font color="Red">LOOKUP(<font color="Green">"zzz",$A2:A2,COLUMN(<font color="Purple">$A2:A2</font>)</font>),0</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

A1 formula copied across.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,037
Office Version
2010
Platform
Windows
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
Joined
Oct 11, 2018
Messages
33
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


ABCDEFGHIJKLMNOPQRSTUVWXYZ
12128
2A10AAA0AAAAA11A11010110A

<tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,102,334
Messages
5,486,218
Members
407,537
Latest member
Frank Nunez

This Week's Hot Topics

Top