counting whole and decimal numbers separately

**deth** · 06-05-2012, 11:16 AM

I want to have formulas that separately counts whole numbers (1,2,3........) and numbers with decimal points (1.1, 1.2, 1.1.1, 1.1.2.........).

Example:
1
1.1
1.1.1
2
2.1
2.1.1

I tried the COUNT formula but counted not only the whole #s 1&2 but also includes 1.1 & 2.1.
I tried the COUNTA formula but counted all numbers (whole and with the decimal points)
I need a formula that counts only the whole number and a separate formula that counts the numbers with decimal points.

Help.

**BarryTSL** · 06-05-2012, 11:25 AM

I can do this by using 2 extra columns to seperate the numbers before summing up.
Use " =IF(B5=INT(B5),B5,0) " where B5 is where your first number is then copy down. This seperates the whole numbers
Then use " =IF(B5<>INT(B5),B5,0) " for the decimals.

**Søren Larsen** · 06-05-2012, 11:28 AM

Assuming your data starts in A1, you could do this instead:

=SUMPRODUCT(((A1:A6)/ROUND(A1:A6,0)=1)*1)

This counts the number of whole numbers. And then you can do this for decimal numbers:

=COUNT(A1:A6)-SUMPRODUCT(((A1:A6)/ROUND(A1:A6,0)=1)*1)