Total Empty column between two characters

[bdtran]

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


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[TD]AA[/TD]
[/TR]
[TR]
[TD]ROW#1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ROW#2[/TD]
[TD]A[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

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]

Hi,

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

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

 

[jtakw]

Hi,

Here's another way:

Book1
	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

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

A1 formula copied across.

 

[bdtran]

Special-K99,

I tested your formula and it worked.

Thank you so much!
BDT

 

[bdtran]

Aryatect,

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

Thanks much for your help.
BDT

 

[bdtran]

Hi,

Here's another way:

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

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>

Sheet688​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A1[/TH]
[TD="align: left"]=IF(AND(A2="",B2="A"),COLUMN()-IFERROR(LOOKUP("A",$A2:A2,COLUMN($A2:A2)),0),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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.

[TABLE="class: grid, width: 1"]
<tbody>[TR]
[TD]A[/TD]
[TD="align: right"]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD="align: right"]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD="align: right"]Q[/TD]
[TD]R[/TD]
[TD="align: right"]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD="align: right"]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD="align: right"]Y[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[/TR]
</tbody>[/TABLE]

I should get

S1 = 1
V1 = 2
Y1 = 2

Thanks,
BDT

 

[jtakw]

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

Book1
	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			1												5		1			2			2
2	A		A	A		A	A	A	A	A	A	A						A		A			A			A
Sheet688

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

A1 formula copied across.

 

[Rick Rothstein]

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]

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


[TABLE="class: grid, width: 393"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD="align: right"]0[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]