# A formula to count with blank cells

#### Arnolf

##### Board Regular
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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### NBVC

##### Well-known Member
I am not getting how you are arriving at those numbers in Count A and Count B?

#### Arnolf

##### Board Regular

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.

#### Greg Truby

##### MrExcel MVP
book2
ABCDEFGHIJKL
112345678910Count ACount B
2A10
3B 8
4AB43
5AB19
6AB41
7AB19
Sheet1

#### Greg Truby

##### MrExcel MVP
<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]

#### NBVC

##### Well-known Member
I guess Greg beat me to it

#### Greg Truby

##### MrExcel MVP
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...

#### Arnolf

##### Board Regular
Greg,
Yes, sir. Thank you
The formulas work a treat!!!.
Very happy with your help
Thank you
Arnolf

#### Arnolf

##### Board Regular
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

#### Greg Truby

##### MrExcel MVP
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?

Replies
6
Views
265
Replies
3
Views
115
Replies
10
Views
228
Replies
9
Views
400
Replies
2
Views
119

1,195,640
Messages
6,010,878
Members
441,571
Latest member
stolenweasel

### 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?

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