Math & Trig
Sum or count whole numbers only (exclude decimal fractions)
Flag integer vs fractional entries with MOD(number, 1) = 0 and aggregate full-unit vs partial-unit rows cleanly with SUMIFS and COUNTIF.
Test fractional remainders with MOD(value, 1) = 0
Dividing any number by 1 leaves only its fractional part: MOD(12, 1) = 0 and MOD(-5, 1) = 0, whereas MOD(4.5, 1) = 0.5 and MOD(2.25, 1) = 0.25 (equivalently, INT(value) = value).
Because SUMIFS criteria accept column references rather than inline MOD(range, 1) transformations, placing =MOD(A2, 1) = 0 in a visible IsWhole helper column lets SUMIFS and COUNTIF audit both whole-case and broken-case inventory.
These helpers assume numeric quantities. Blank cells can behave like zero and be counted as whole numbers; for mixed input use =IF(ISNUMBER(A2),MOD(A2,1)=0,FALSE). Calculated values may have floating-point residues, so define any rounding policy explicitly.
| Qty (A) | IsWhole (B) | Whole Contribution | Fractional Contribution |
|---|---|---|---|
| 12 | TRUE | 12 | 0 |
| 4.5 | FALSE | 0 | 4.5 |
| 8 | TRUE | 8 | 0 |
| 2.25 | FALSE | 0 | 2.25 |
| -5 | TRUE | -5 | 0 |
Sum and count whole integers vs decimals
Summing rows where IsWhole = TRUE totals 12 + 8 + (-5) = 15 across 3 integer rows, while FALSE totals 4.5 + 2.25 = 6.75.
=SUMIFS(A2:A6, B2:B6, TRUE)Count whole entries and sum fractional entries
COUNTIF(B2:B6, TRUE) returns 3 whole-number rows; SUMIFS(A2:A6, B2:B6, FALSE) returns 6.75.
=SUMIFS(A2:A6, B2:B6, FALSE)Try the example
Use the original values or change them to test your understanding.