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

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>

 

[Rick Rothstein]

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>

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),"")

 

[bdtran]

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

 

[bdtran]

Jtakw,

I replaced "A" with "zzz" as mentioned and it returned the corrected result.

Thanks again with your help.
BDT

 

[jtakw]

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:

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
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
Sheet688 (2)

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

Formula copied across.

 

[bdtran]

Thanks Jtakw,

I tested your formula and it worked with expected result.

 

[jtakw]

You're welcome.