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.