Google Sheets: Sum if value is round number

user_15024238 0
Viewed 182

I'm sure there's a pretty simple solution, but I cant' get to it.

I'm trying to sum a list of numbers, but only the values that are round numbers/whole integers.

Eg column A:

1
1.5
3
2.4
2

sum of the whole numbers

1 + 3 + 2 = 6

Any hint?

google-sheets-formula formula google-sheets integer sum
5 Answers

Let's suppose your numbers list begins in A2 and runs downward (i.e., A2:A). You can use this:

=SUM(FILTER(A2:A,A2:A=INT(A2:A)))

In plain English, this reads as follows: "Sum only those numbers in A2:A where the original value is the same as the integer-only portion of that value."

user_139032830

Try:

=sumproduct((A1:A5)*(A1:A5=int(A1:A5)))

this will also work in Excel

enter image description here

user_24746560

Another solution, you can use SEARCH to search for the decimal point:

=SUM(A1:A)-SUM(FILTER(A1:A,SEARCH(".",TO_TEXT(A1:A))))

enter image description here

or =SUM(FILTER(A1:A,NOT(ISNUMBER(SEARCH(".",A1:A)))))

as JvdV mentioned in his comment.

user_112252910

use dot detection:

=INDEX(SUM(IF(REGEXMATCH(""&A:A, "\."),,A:A)))

enter image description here

user_56326290

Either try QUERY():

=SUM(QUERY(A:A,"where A matches '\d+'"))

Or FILTER():

=SUM(FILTER(A:A,MOD(A:A,1)=0))

enter image description here

Note: This 1st option makes use of the possibility to use a regular expression inside the "where" clause of QUERY(). Use =SUM(QUERY(A:A,"where A matches '-?\d+'")) if you want to account for positive and negative integers.

user_97581940
Related