beta

Spreadsheet formulas in PaperSheets: SUM, IF, COUNTIF and more

By THEO GRIG MEDIA PRO SRL • Last updated:

In PaperSheets a formula starts with = and can use numbers, cell references such as A1, ranges such as A1:A5 and 40 built-in functions. The result updates when the cells it uses change.

Open PaperSheetsFree, no account, files stay on your device

Write your first formula in 5 steps

  1. Click a cell and type = followed by the formula, for example =A1+B1*2. You can use + - * / ^ and brackets, & to join text, and % for percentages.
  2. To add up a range, type =SUM(A1:A5). While you type a formula, click a cell to insert its reference.
  3. Press Enter to confirm the formula, or Esc to cancel. Press F2 to edit a cell again.
  4. To copy a formula down or to the right, select the cells and press Ctrl+D or Ctrl+R. References move with the formula: A1 becomes A2. Write $A$1 to keep a reference fixed.
  5. For a quick total, select the empty cell under a column of numbers and choose AutoSum in the Insert menu.

The 40 functions of PaperSheets

GroupFunctions
Totals and statisticsSUM, AVERAGE (or AVG), MIN, MAX, COUNT, COUNTA, PRODUCT, MEDIAN
ConditionsCOUNTIF, SUMIF, AVERAGEIF
Rounding and mathsROUND, ROUNDUP, ROUNDDOWN, INT, ABS, SQRT, POWER, MOD, EXP, LN, LOG10, PI
Logic and checksIF, IFERROR, AND, OR, NOT, ISNUMBER, ISBLANK
TextCONCAT (or CONCATENATE), LEN, UPPER, LOWER, TRIM, LEFT, RIGHT, MID
Date and timeTODAY, NOW

Examples you can copy

GoalFormula
Total of a column=SUM(B2:B20)
Average of the grades=AVERAGE(C2:C30)
Pass or fail=IF(C2>=5,"Pass","Fail")
Count the cells that say paid=COUNTIF(A2:A50,"paid")
Count the amounts above 100=COUNTIF(B2:B50,">100")
Add up only the rows of one category=SUMIF(A2:A50,"food",B2:B50)
Hide an error such as a division by zero=IFERROR(A2/B2,0)
Share of a total (then choose the Percent format)=B2/SUM($B$2:$B$20)
Join first name and last name=CONCAT(A2," ",B2)
Round to two decimals=ROUND(A2,2)

Errors and limits

An error appears in the cell instead of the result: #DIV/0! for a division by zero, #NAME? for a function or name PaperSheets does not know, #VALUE! when text is used where a number is needed, #NUM! for an impossible number such as the square root of a negative number, #REF! for a cell that does not exist (for example after you delete its row or column), #CIRC! when a formula refers to itself and #ERROR! when the formula cannot be read, for example a missing bracket.

A column is one letter (A to Z) and a sheet has up to 500 rows. Lookup functions are not available. TODAY and NOW return the date as text, so they are for showing a date, not for calculating with dates.

Frequently asked questions

Which formulas does PaperSheets support?

It supports 40 functions: totals and statistics (SUM, AVERAGE, MIN, MAX, COUNT and more), conditions (COUNTIF, SUMIF, AVERAGEIF), rounding and maths, logic (IF, IFERROR, AND, OR, NOT), text and the date functions TODAY and NOW.

How do I count or add up only some rows?

Use COUNTIF to count the cells that match a condition and SUMIF to add up the matching rows, for example =SUMIF(A2:A50,"food",B2:B50). AVERAGEIF works the same way for an average.

How do I keep a cell reference from changing when I copy a formula?

Write a dollar sign before the letter and the number, for example $B$2. Without it, the reference moves with the formula.

Do formulas update by themselves?

Yes. When you change a cell, every formula that uses it is calculated again.

Is there a lookup function?

No. PaperSheets does not have lookup functions yet. SUMIF, COUNTIF and IF cover many everyday cases.

Documentation

More free tools from Oxyde