Counting Problem

I am trying to find a way of counting decimal numbers if, say, they begin with 3.

For example, I might have 3.33, 4.1, 3.0, 5.65, 3.8, 3.7, 3.33, etc.

I want to count anything that begins with 3 (3.33, 3.0, etc). Using the data above the answer would be 5 ...5 numbers that begin with a 3.

If this possible?

There doesn't appear to be the facility with Countif. I don't want to truncate the data (the spreadsheet already is quite extensive). I have tried using =SUMPRODUCT((Y$2:Y$170 >=3)*(Y$2:Y$170<=3.9)) but this will return an answer only when there is a complete set of data within cells within the range.

I feel sure there must be a way but its driving me nuts! : - )

Any help would be apprecaited.

Aphid

Hi,

Say your numbers in Col A. Try

=SUMPRODUCT(--(A1:A100>=3)*(--(A1:A100<4)))

VBA Noob

Thanks for your help.

The SUMPRODUCT idea didn't work I am afraid.

The Countif variation did in a way although I would appreciate some clarification.

Will I also be able to count all numbesr strating with a 4, with a 5, 6, 7, etc...
Alhtough 3 is the lowest value, I need to be able to count more than just 3's.

Aphid

Sorry but sumproduct does work

Just change the range

=SUMPRODUCT(--(Y2:Y170>=3)*(--(Y2:Y170<4)))

If you want to add 3's, 4's etc enter this in say X1

=SUMPRODUCT(--(Y2:Y170>=ROW(A3))*(--(Y2:Y170<ROW(A4))))

and drag down

VBA Noob

I must be doing something wrong here.

When I put the formula at the bottom of the column I get a div/0! error.

This only clears when there are values in all the cells within the range. However, with my data set there will be situations when there will be bits of data missing. That being the case, the formula won't give a result. ....?

Can you post and example of your data then and your expected results

VBA Noob

If countif works for you try

=COUNTIF($Y$2:$Y$170,">="&ROW(A3))-COUNTIF($Y$2:$Y$170,">="&ROW(A4))

and drag down

Note. Sumproduct doesn't work for column Can only do Y1:Y65535

VBA Noob

If your values will extend into the thirties or hundreds i.e. 33.33, 300.55 and you still want to count those because the begin with 3 you would need to use a different solution.


Try,

=SUMPRODUCT(--(LEFT(A1:A100,1)="3"))

This is where your sample data is in A1:A100. Change to suit your needs.

If you only want to count values of 3.whatever then you could also try,

=SUMPRODUCT(--(INT(A1:A100)=3))

HTH

Steve

All the helpful advice and suggestions has been fantastic and greatly appreciated. Many thanks. I have been busy trying them with my data.

The solution that appears to work is the last one from Steve:
=SUMPRODUCT(--(INT(A1:A100)=3))

This seems to count anything that begins with a 3, rather like including a wildcard in the request, count 3.* for example.

Aphid