How to sum only whole numbers in Excel?
Supposing there are both whole numbers and decimal numbers populated in your worksheet, but, you just want to sum only the whole numbers from the data range, do you have any quick and easy formulas to solve this task?
Sum only whole numbers with formulas in Excel
Sum only whole numbers with formulas in Excel
The following formulas may help you to identify and add only the whole numbers, please do with following steps:
1. Enter the following formula into a blank cell where you want to put the result: =SUMPRODUCT(--(MOD(A1:C10,1)=0),A1:C10) (A1:A10 is the data range that you want to sum the whole numbers, you can change it to your need ), see screenshot:
2. And then press Enter key, and you will get the total value of all whole numbers in the range.
Tip: Except the above formula, here is another formula also can help you: =SUMPRODUCT((A1:C10=INT(A1:C10))*(A1:C10)).
Related articles:
How to sum multiple columns based on single criteria in Excel?
How to sumif with multiple criteria in one column?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
