Count cells with a Certain Custom Number Format?

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,245
Office Version
  1. 365
Platform
  1. Windows
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?
 

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.
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
BCDEF
170.00"*"
2
315General
415General
515General
60.00"*"
7General
815General
915.00*0.00"*"
1015General
11(15.00)0.00_);[Red](0.00)
1215General
1315.00*0.00"*"
1415General
1515/08/09d/mm/yy;@
1615.00*0.00"*"
1725.60*0.00"*"
18-23.36*0.00"*"
19erwer@
2015General
2115.00*0.00"*"
2215General
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
 
Upvote 0
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.
 
Upvote 0
mgirvin,

Excel Workbook
ABC
11.003
21.00
3$100.00
41
51.00
61.00%
71
81.00
9100.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


Code:
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
 
Upvote 0
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!
 
Upvote 0
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
BCDEF
170.00"*"
2
3150
4150
5150
61
70
8150
915.00*1
10150
11(15.00)0
12150
1315.00*1
14150
1515/08/090
1615.00*1
1725.60*1
18-23.36*1
19erwer0
20150
2115.00*1
22150
Count Custom No Format
 
Upvote 0
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!
 
Upvote 0
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""*""")
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

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