fleskj

08-05-2013, 04:28 AM

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.

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.