Sumproduct instead of countif. 1 digit numbers problem.

Hello, VBA express! :hi:
Firstly, I'd like to thank you all for the helpful tips I've found on this forum so far, keep it up!
However, there is one thing that I am yet to find a solution for :)

Here is the deal: I've got a data set with numbers, let's say:

	A1
1	325
2	562
3	0
4	95
5	568
6	327
7	182
8	962
9	168
10	207

When I need to find the number of times cells in A1:A10 start with 5, I use:
=SUMPRODUCT((LEFT(A1:A10)+0=5)+0) //this is the simplest formula I could find
and it works great, it returns "2", just as it should, meaning that there are 2 cells that start with 5.

But, if I need a count of the number of times the second digit is 6 and use this formula:
=SUMPRODUCT((MID(A1:A10,2,1)+0=6)+0)
it returns a #VALUE! error, as there are cells in this range that contain 1 digit only.

I could get around this problem by taking a sum of the following formulas:
=SUMPRODUCT((LEFT(A1:A10,2)+0=16)+0)+SUMPRODUCT((LEFT(A1:A10,2)+0=26)+0)+...+SUMPRODUCT((LEFT(A1:A10,2)+0=96)+0
Yet, I wish to find a simpler formula, and I might also need a 3rd digit check later for the research depending on the results of the 2nd one, and I'd need to list a hundred numbers with 3rd digit being 6 and another hundred for a any other number.
The use of wildcards didn't work for me, but then again I'm a novice :dunno

Please help :)

P.S. These formulas only work if there no empty or non-numeric cells.
P.P.S. There is a longer formula that also works for the LEFT function, if it's of any help:
=SUMPRODUCT(0+(VALUE(LEFT(A1:A10+0))=5)) // for the count of cells that start with 5.

Here is a UDF (User Defined Function) that will work. You must open the VBA editor, (right click on a sheet tab and select "View Code.")

In the Editor make sure that the "Project Explorer" window is in View, (View Menu.)

Right Click on the VBAProject(Your Workbook Name) and select Insert >> Module.

Double click on that new module in the Project Explorer and paste this code in the right hand pane of the Editor.

Code:

---

Option Explicit

FunctionCountDigits(RangeToAnalyze As Range, _
                              DigitPosition As Integer, _
                              DigitToCount As Integer As Long
                   
Dim Cel As Range
Dim X As Long

For Each Cel In RangeToAnalyze
    If Mid(CStr(Cel), DigitPosition, 1) = CStr(DigitToCount) Then
        X = X + 1
    End If
Next Cel

CountDigits = X
End Function

---

To use this Function in your worksheet, in a Cell, type the = sign, then click the fx button on the formula bar to Insert a Function. At the bottom of the "or Select a Category" dropdown list, select "User Defined Function" and select CountDigits. Then follow the Wizard instructions.

You can also type in the Cell

Quote:

---

=CountDigits(

---

Then Select the Range you want to use, then type a comma, the digit position to count, then a comma, the the digit to count, and a closing parenthesis.

Code:

---

=CountDigits($A$1:$A$10,3,1)

---

That example will count all the 1's in the third position. By using the Dolloar signs in the Range assignment, you can copy and paste the formula, and only have to change the position and digit in the new location.

The Function is CountDigits(RaPiDly) or R for Range, P for Position, D for Digit. It should work for numbers up to 32,000 digits long :bug:

I have to admit, this is awesome :) I have never used VBA before, but your step by step guide is foolproof.
Thank you so much, it works great!
I wish I understood the code itself, but it's a different matter, VBA is on to-do list.
Thanks again, SamT, you saved my dissertation. :bow:

SUMPRODUCT will do it

Code:

---

=SUMPRODUCT(--(MID(A1:A10,2,1)="6"))

---

Quote:

---

Originally Posted by xld

SUMPRODUCT will do it

=SUMPRODUCT(--(MID(A1:A10,2,1)="6"))

---

Ha! I was wondering if there is a simple formula :) Thanks, xld!
Quick question though, how can I replace this "6" (with the quotes) with a cell reference?
If I've got 6 in the B6 cell for example,
=SUMPRODUCT(--(MID(A1:A10,2,1)=B6)) won't work :dunno

Quote:

---

I have to admit, this is awesome :) I have never used VBA before, but your step by step guide is foolproof.
Thank you so much, it works great!
I wish I understood the code itself, but it's a different matter, VBA is on to-do list.
Thanks again, SamT, you saved my dissertation. :bow:

---

In VBA, Place the Keyboard cursor inside a word and press F1 for help on that word.

Quote:

---

Originally Posted by fleskj

Ha! I was wondering if there is a simple formula :) Thanks, xld!
Quick question though, how can I replace this "6" (with the quotes) with a cell reference?
If I've got 6 in the B6 cell for example,
=SUMPRODUCT(--(MID(A1:A10,2,1)=B6)) won't work :dunno

---

I've figured it out,
it should be
=SUMPRODUCT(--(MID(A1:A10,2,1)+0=B6)) for it to work :)

EDIT: scratch that, it leaves me with the original problem of 1 digit cells, nevermind :D

Quote:

---

Originally Posted by SamT

In VBA, Place the Keyboard cursor inside a word and press F1 for help on that word.

---

Thanks! I'll look into that.

It doesn't work for the same reason that your original formula doesn't work, you are comparing numeric items with strings. You need to string it

Code:

---

=SUMPRODUCT(--(MID(A1:A10,2,1)=TEXT(B6,"General")))

---

As your A1 column is text then it can be traeted as such, if you use =SUMPRODUCT((MID(A1:A10&"000",2,1)+0=6)+0) then you will find it will work