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
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
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
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),"")
Thanks!
- forum use guidelines, forum rules and terms of use
- Try searching for your answer first, see how
- Read the FAQs
- List of BB codes
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.
Last edited by jtakw; Jul 15th, 2019 at 12:15 PM.
Special-K99,
I tested your formula and it worked.
Thank you so much!
BDT
Aryatect,
I used for your formula and got the result I am looking for.
Thanks much for your help.
BDT
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
Not sure why, but just swap out the "A" with "zzz" like below:
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
Worksheet Formulas
Cell Formula A1 =IF(AND(A2="",B2="A"),COLUMN()-IFERROR(LOOKUP("zzz",$A2:A2,COLUMN($A2:A2)),0),"")
A1 formula copied across.
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),"")
Rick's "mini" blog... http://www.excelfox.com/forum/f22/
.
Want to post a small screen shot? See Part B here.
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
Like this thread? Share it with others