Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting function but not double counting duplicates | Excel Worksheet Functions | |||
counting if | Excel Worksheet Functions | |||
I need help has to do with counting | Excel Discussion (Misc queries) | |||
Counting rows, then counting values. | Excel Discussion (Misc queries) | |||
Counting names in a column but counting duplicate names once | Excel Discussion (Misc queries) |