Count values in a range of cells
 

I have a range of cells with values that are 5 or 10 numbers long that start
with 11, 12 or 13. What function do I use to tell Excel to count only the
cells which value starts with 11? I do not want to sum, only count the number
of cells that have values starting with 11. Again, the values can be 5
digits, as in 11222 or 10 digits such as 1122222222. I only want Excel to
return the number of values that start with 11 regardless of how long the
digit actually is.

Count values in a range of cells
 

=SUMPRODUCT(--(LEFT(A1:A20,2)="11"))


--


Regards,


Peo Sjoblom


"Loralei" wrote in message
...
I have a range of cells with values that are 5 or 10 numbers long that
start
with 11, 12 or 13. What function do I use to tell Excel to count only the
cells which value starts with 11? I do not want to sum, only count the
number
of cells that have values starting with 11. Again, the values can be 5
digits, as in 11222 or 10 digits such as 1122222222. I only want Excel to
return the number of values that start with 11 regardless of how long the
digit actually is.

Count values in a range of cells
 

Assuming your values are in A1:A100, try this:

=SUMPRODUCT(--(LEFT(A1:A100,2)="11"))

Adjust the range to suit.

Hope this helps.

Pete


On Nov 13, 10:57 pm, Loralei
wrote:
I have a range of cells with values that are 5 or 10 numbers long that start
with 11, 12 or 13. What function do I use to tell Excel to count only the
cells which value starts with 11? I do not want to sum, only count the number
of cells that have values starting with 11. Again, the values can be 5
digits, as in 11222 or 10 digits such as 1122222222. I only want Excel to
return the number of values that start with 11 regardless of how long the
digit actually is.

Count values in a range of cells
 

Try this:

=SUMPRODUCT(--(LEFT(A1:A10,2)="11"))

--
Biff
Microsoft Excel MVP


"Loralei" wrote in message
...
I have a range of cells with values that are 5 or 10 numbers long that
start
with 11, 12 or 13. What function do I use to tell Excel to count only the
cells which value starts with 11? I do not want to sum, only count the
number
of cells that have values starting with 11. Again, the values can be 5
digits, as in 11222 or 10 digits such as 1122222222. I only want Excel to
return the number of values that start with 11 regardless of how long the
digit actually is.