A formula to count with blank cells

Arnolf

Board Regular
Joined
Sep 18, 2005
Messages
78
Hello,
One more time asking for your help.

Perhaps, I have a silly request.
I need to count A and B in a range. Enclose my Sheet.

I know that I can do it filling my range with all A's or B's I'd need, then I'd use COUNTIF. I could hide similar values with Conditional Formatting and that would do the trick.
But, I'd really appreciate someone help me to do it with my example format.
Is this possible ?.
Thank you so much for your assistance.
Arnolf.
Book2.xls
ABCDEFGHIJKLMN
1
212345678910COUNT of ACOUNT of B
3A10
4B8
5AB43
6
7AB19
8AB41
9
Sheet1
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
I am not getting how you are arriving at those numbers in Count A and Count B?
 
Upvote 0
NBVC thank your for your reply.

For my first register:
I have an A in column 1, all the cells to the right are blanks so they are considered as an A. The result is 10 (count column 1, column 2, 3 , 4, 5, 6 ,..., 10)

For my second register:
I have a B in column 3, all the cells to the right are blanks so they are considered as a B. The result is 8 (count column 3 , columns 4, 5, 6 ,..., 10)

For my third register:
I have an A in column 4, all the cells to the right are blanks until column 8 where is a B. So I count A's for columns 4, 5, 6 and 7. And I count B's for columns 8, 9 and 10.
 
Upvote 0
<ul>[*]K2=IF(ISNA(MATCH("A",$A2:$J2)),"",10-MATCH("A",$A2:$J2,0)+1-IF(ISNA(MATCH("B",$A2:$J2,0)),0,10-MATCH("B",$A2:$J2,0)+1))[*]L2=IF(ISNA(MATCH("B",$A2:J2,0)),"",10-MATCH("B",$A2:$J2,0)+1)[/list]
 
Upvote 0
You may note that we're not "counting" anything here. We're using the positions. If you wanted to actually "count" blanks you'd need something like {Sum(--IsBlank(A2:J2))} but then you end up needing to use Match to locate the "A" and "B" anyway, so there're really no point in "counting" the blanks...
 
Upvote 0
Greg,
Yes, sir. Thank you
The formulas work a treat!!!.
Very happy with your help
Thank you
Arnolf
 
Upvote 0
I feel so dumb to ask this again. I apologize
Greg gave me a fantastic solution for my request.
What I forgot was to enclose another example. The formulas dont return the desired results with the next data.
I attach my Sheet.
So, I will highly appreciate you give me a hand.
rgds,
Arnolf.
Book2.xls
ABCDEFGHIJKLMN
112345678910COUNT of ACOUNT of BCOUNT of ACOUNT of B
2BABA 853
3BA-7817
4BABABABA 1055
5  
6BA-61046
7AB4646
8AB7171
9Greg's formulasResults needed
10
Sheet1
 
Upvote 0
Alright - row 7 & 8 are ok?
I think I understand how you got {4,6} for row 6. But I'm not picking up on the logic to generate solution sets {5,3;1,7;5,5} in rows 2, 3 & 4. Can you elaborate?
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top