Counting a age
 

Hi i have a spreadsheet that has a column of different ages e.g

12.1
12.3
12.2
15.6
47.3
33.6
12.1
15.6
12.2

i need to be able to count the number of 12.1 there are in this column.

One of the problems i have is that the data comes for a query that is
created in Access so in the formula bar the cell contents look like
21.1663244353183, if i use roundup it then changes the contents.

Can anyone help with a formula please.

Counting a age
 

On way:
=SUMPRODUCT(--((INT(A1:A10*10)/10)=21.1))

(I used 21.1, not 12.1)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Twb wrote:

Hi i have a spreadsheet that has a column of different ages e.g

12.1
12.3
12.2
15.6
47.3
33.6
12.1
15.6
12.2

i need to be able to count the number of 12.1 there are in this column.

One of the problems i have is that the data comes for a query that is
created in Access so in the formula bar the cell contents look like
21.1663244353183, if i use roundup it then changes the contents.

Can anyone help with a formula please.

--

Dave Peterson

Counting a age
 

Thanks Dave, really appreciate your help and time.

"Dave Peterson" wrote:

On way:
=SUMPRODUCT(--((INT(A1:A10*10)/10)=21.1))

(I used 21.1, not 12.1)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Twb wrote:

Hi i have a spreadsheet that has a column of different ages e.g

12.1
12.3
12.2
15.6
47.3
33.6
12.1
15.6
12.2

i need to be able to count the number of 12.1 there are in this column.

One of the problems i have is that the data comes for a query that is
created in Access so in the formula bar the cell contents look like
21.1663244353183, if i use roundup it then changes the contents.

Can anyone help with a formula please.

--

Dave Peterson

Counting a age
 

Hi Dave, this formula works well however just noticed that it will not accept
47.10 or any number that is **.10 in the formula, it shows it as
=SUMPRODUCT(--((INT(A1:A10*10)/10)=47.1)), it does accept 47.11, spent the
whole weekend trying different variations but to no joy, can you explain why
this would be and a solution.
It's the same in either 2007 or 2003.
Many thanks


"Dave Peterson" wrote:

On way:
=SUMPRODUCT(--((INT(A1:A10*10)/10)=21.1))

(I used 21.1, not 12.1)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Twb wrote:

Hi i have a spreadsheet that has a column of different ages e.g

12.1
12.3
12.2
15.6
47.3
33.6
12.1
15.6
12.2

i need to be able to count the number of 12.1 there are in this column.

One of the problems i have is that the data comes for a query that is
created in Access so in the formula bar the cell contents look like
21.1663244353183, if i use roundup it then changes the contents.

Can anyone help with a formula please.

--

Dave Peterson