SUBTOTAL in Excel only works with integer numbers.

Copper Contributor

SUBTOTAL in Excel only works with integer numbers.  When I try to sum decimal numbers it always returns zero.

4 Replies


@David_Plane_1939 wrote:

SUBTOTAL in Excel only works with integer numbers.  

The statement is simply not true.  Perhaps you formula is trying to count the entries rather than sum them?

Thank you for your reply. I have used the Excel SUBTOTAL function successfully for years, with no problems. I am indeed asking for the SUM. Unfortunately NOW it seems to have gone haywire and only sums (correclty by the way) integer number.



Most likely, the problem is:  you and your computer do not agree on what the decimal point and thousands separator should be.  Or for some other reason, Excel is treating the values as text.


Looks can be deceiving, and the format of the cells do not matter.


For the cells with numbers that are being ignored, use formulas of the form =ISTEXT(A1) to confirm that they are interpreted as text, not numbers.


There can be many reasons.  Some things to look for:


1. Wayward characters in the data, notably CHAR(160), which looks like a space.

2. Incompatible currency symbol.

3. Incompatible decimal point and/or thousands separator.

4. Etc, etc, etc


If you need assistance, attach an example Excel file the duplicates the problem, or provide a download link to the file on a file-sharing website.

Many thanks. The decimal data cells were "TRUE" - i.e. logical entries. I used the Value function zo change to number format, and it works.
Many thanks again.