Your second formula works for me, but not the first.

Putting the 4 numbers 1, 1.1, 1.2, 1.3, into A1:A4, and writing the formula

=SUMPRODUCT((INT(A1:A4)0)*1)

gives a result of 4, not 1. You are, in effect, just counting the number of
cells containing numbers = 1.

You can get the correct result of 1 by using your 2nd formula but change 0 to
=0.

These formulas will also work:

to count integers: =SUMPRODUCT(--(INT(A1:A11)=A1:A11))
to count non-integers: =SUMPRODUCT(--(INT(A1:A11)<A1:A11))


On Thu, 28 Oct 2004 07:51:05 -0700, LanceB
wrote:

=SUMPRODUCT((INT(A1:A11)0)*1) ' for the interger count
=SUMPRODUCT((A1:A11-INT(A1:A11)0)*1) 'for the fractional count



"simonkf" wrote:


I need to create a formula/macro that will scroll down a column,
counting the number of instances of data. I think this is better
explained by the example below

1
1.1
1.2
1.3

2
2.1
2.2
2.3
2.4
2.5
I need to count the number of whole numbers and also the number of
decimals under each one. Each set of numbers is variable in length, 1
may have 3 steps and another 100s.

If any one can help that'd be much appreciated.
Thanks
Simon


--
simonkf
------------------------------------------------------------------------
simonkf's Profile:
http://www.excelforum.com/member.php...o&userid=15821
View this thread: http://www.excelforum.com/showthread...hreadid=273137