Count cells with a Certain Custom Number Format?

[mgirvin]

Dear Most Amazing Excelers In The World,

If I have a column of numbers and some (not all) of the numbers have the Custom Number Format:

0.00”*”

is there a way to count the numbers with this Custom Number Format?

 

[Peter_SSs]

Mike

I see a couple of ways. Given the setup in this sheet where the data is in column B and the yellow cells are formatted with your Custom Format and the others are not.

Excel Workbook
	B	C	D	E	F
1				7	0.00"*"
2
3	15	General
4	15	General
5	15	General
6		0.00"*"
7		General
8	15	General
9	15.00*	0.00"*"
10	15	General
11	(15.00)	0.00_);[Red](0.00)
12	15	General
13	15.00*	0.00"*"
14	15	General
15	15/08/09	d/mm/yy;@
16	15.00*	0.00"*"
17	25.60*	0.00"*"
18	-23.36*	0.00"*"
19	erwer	@
20	15	General
21	15.00*	0.00"*"
22	15	General
23
Count Custom No Format

#VALUE!

</td></tr></table></td></tr></table>




Method 1 (vba)

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Count_Format()<br>    <SPAN style="color:#00007F">Dim</SPAN> myFormat <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> myRange <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> Counter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    myFormat = "0.00""*"""<br>    <SPAN style="color:#00007F">Set</SPAN> myRange = Range("B3", Range("B" & Rows.Count).End(xlUp))<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> myRange<br>        <SPAN style="color:#00007F">If</SPAN> c.NumberFormat = myFormat <SPAN style="color:#00007F">Then</SPAN><br>            Counter = Counter + 1<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> c<br>    MsgBox Counter<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Method 2 (GET.CELL)
Assuming the next column (C in this case) is available to use as a helper column.

a) Insert|Name|Define...|Names in workbook: myNumFormat|Refers to: =GET.CELL(7,OFFSET(INDIRECT("RC",FALSE),0,-1))|OK

b) Formula in C3 (copied down): =myNumFormat

c) Your format typed into F1

d) Formula as shown in E1

 

[Peter_SSs]

I just realised that my suggestion counts the cells with the Custom Format. If you only want to count cells with that Format and that contain numbers (or are not blank) then slight modifications would be needed. Post back with more details if you need help with that.

 

[hiker95]

mgirvin,

Excel Workbook
	A	B	C
1	1.00		3
2	1.00
3	$100.00
4	1
5	1.00
6	1.00%
7	1
8	1.00
9	100.00
10
Sheet1

The function in cell C3:
=CountMyFormat(A1:A10)



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below Function code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Private Function CountMyFormat(MyRange As Range) As Long
'
' Cell C1 formula:
' =CountMyFormat(range of cells)
' =CountMyFormat(A1:A10)
'
Dim rng As Range, c As Range, cntr As Long
Set rng = MyRange
cntr = 0
For Each c In rng
  If c.NumberFormat = "0.00”*”" Then cntr = cntr + 1
Next c
CountMyFormat = cntr
End Function

 

[mgirvin]

Peter_SSs,

Wow! Once again you have helped me out greatly! The GET.CELL is just what I needed. I did not know about this old Macro 4 trick. I just did a Google search and found some great infro about this (the first hit was a Mr Excel Message Board post from 2002)!!!!!

Totally cool trick!

 

[mgirvin]

hiker95,

Thanks for your code!

 

[Peter_SSs]

I just did a Google search and found some great infro about this (the first hit was a Mr Excel Message Board post from 2002)!!!!!

Probably this one which I referred to in drafting my reply.

Perhaps a neater way on the sheet would have been this:

Excel Workbook
	B	C	D	E	F
1				7	0.00"*"
2
3	15	0
4	15	0
5	15	0
6		1
7		0
8	15	0
9	15.00*	1
10	15	0
11	(15.00)	0
12	15	0
13	15.00*	1
14	15	0
15	15/08/09	0
16	15.00*	1
17	25.60*	1
18	-23.36*	1
19	erwer	0
20	15	0
21	15.00*	1
22	15	0
Count Custom No Format

 

[mgirvin]

That's it! That is the post I found also.

I just took your advice about taking into consideration the fact that some of the cells could be blank and have the Custom Number Formatting. Those cells should not be counted and so I used:

=--AND(C4<>"",myNumFormat="0.00""*""")

where C4 is one cell to the left of the cell with the formula.

Thanks again!

 

[Peter_SSs]

I just took your advice about taking into consideration the fact that some of the cells could be blank and have the Custom Number Formatting. Those cells should not be counted and so I used:

=--AND(C4<>"",myNumFormat="0.00""*""")

Yes, that's the sort of thing I had in mind. Or the same thing a different way:
=(C4<>"")*(myNumFormat="0.00""*""")

 

[mgirvin]

I messed around with this some more (because it is such a great trick) and instead of using:

=GET.CELL(7,OFFSET(INDIRECT("RC",FALSE),0,-1))

I have replaced the "one cell to my left" formula construct:

OFFSET(INDIRECT("RC",FALSE),0,-1)

With:

!A4

given that the cell B4 was selected when I opened define name to get this shorter version:

=GET.CELL(7,!A4)

I have used this "global relative cell reference" trick in other defined names (and it works just fine), but so far, it also seems to be working with this xl 4 macro function, GET.CELL.

Thanks again, Peter_SSs!