Quip Spreadsheet Formulas - Quick Reference Guide

Quip Spreadsheet Formulas - Quick Reference Guide

📊 MATH & STATISTICS

SUM - Adds all numbers in a range
Example: =SUM(A1:A10)

AVERAGE - Returns the average of numbers
Example: =AVERAGE(B1:B10)

COUNT - Counts how many cells contain numbers
Example: =COUNT(C1:C10)

COUNTA - Counts non-empty cells
Example: =COUNTA(D1:D10)

MAX - Returns the largest value
Example: =MAX(E1:E10)

MIN - Returns the smallest value
Example: =MIN(F1:F10)

ROUND - Rounds a number to specified decimals
Example: =ROUND(A1, 2)

ABS - Returns absolute value
Example: =ABS(-25) returns 25

MULTIPLY - Multiplies two numbers
Example: =MULTIPLY(A1, B1)

DIVIDE - Divides two numbers
Example: =DIVIDE(A1, B1)

📝 TEXT FUNCTIONS

CONCATENATE - Combines text strings
Example: =CONCATENATE(A1, " ", B1)

CONCAT - Combines strings (newer version)
Example: =CONCAT(A1:C1)

LEFT - Returns leftmost characters
Example: =LEFT(A1, 5)

RIGHT - Returns rightmost characters
Example: =RIGHT(A1, 3)

MID - Extracts characters from middle
Example: =MID(A1, 2, 5)

LEN - Returns length of text
Example: =LEN(A1)

UPPER - Converts text to uppercase
Example: =UPPER(A1)

LOWER - Converts text to lowercase
Example: =LOWER(A1)

TRIM - Removes extra spaces
Example: =TRIM(A1)

SUBSTITUTE - Replaces text in a string
Example: =SUBSTITUTE(A1, "old", "new")

🔍 LOOKUP & REFERENCE

VLOOKUP - Vertical lookup in a table
Example: =VLOOKUP(A1, B:D, 2, FALSE)

HLOOKUP - Horizontal lookup in a table
Example: =HLOOKUP(A1, B1:D10, 2, FALSE)

INDEX - Returns value at row/column intersection
Example: =INDEX(A1:C10, 2, 3)

MATCH - Returns position of value in range
Example: =MATCH("Apple", A1:A10, 0)

CHOOSE - Returns value from list by index
Example: =CHOOSE(2, "Red", "Blue", "Green")

✅ LOGICAL FUNCTIONS

IF - Performs logical test
Example: =IF(A1>10, "Yes", "No")

AND - Returns TRUE if all conditions are TRUE
Example: =AND(A1>5, B1<10)

OR - Returns TRUE if any condition is TRUE
Example: =OR(A1>5, B1<10)

NOT - Inverts logical value
Example: =NOT(A1=B1)

IFERROR - Returns alternative if error
Example: =IFERROR(A1/B1, "Error")

IFNA - Returns alternative if #N/A
Example: =IFNA(VLOOKUP(...), "Not Found")

📅 DATE & TIME

TODAY - Returns current date
Example: =TODAY()

NOW - Returns current date and time
Example: =NOW()

DATE - Creates date from year/month/day
Example: =DATE(2026, 6, 2)

YEAR - Extracts year from date
Example: =YEAR(A1)

MONTH - Extracts month from date
Example: =MONTH(A1)

DAY - Extracts day from date
Example: =DAY(A1)

WEEKDAY - Returns day of week (1-7)
Example: =WEEKDAY(A1)

NETWORKDAYS - Counts workdays between dates
Example: =NETWORKDAYS(A1, B1)

EDATE - Date x months before/after
Example: =EDATE(A1, 3)

DATEDIF - Calculates difference between dates
Example: =DATEDIF(A1, B1, "D")

🎯 CONDITIONAL COUNTING & SUMMING

COUNTIF - Counts cells meeting criteria
Example: =COUNTIF(A1:A10, ">5")

COUNTIFS - Counts cells meeting multiple criteria
Example: =COUNTIFS(A:A, "Complete", B:B, ">100")

SUMIF - Sums cells meeting criteria
Example: =SUMIF(A1:A10, ">5", B1:B10)

SUMIFS - Sums cells meeting multiple criteria
Example: =SUMIFS(C:C, A:A, "Complete", B:B, ">100")

AVERAGEIF - Averages cells meeting criteria
Example: =AVERAGEIF(A1:A10, ">5")

AVERAGEIFS - Averages cells meeting multiple criteria
Example: =AVERAGEIFS(C:C, A:A, "Complete", B:B, ">100")

📊 DATA ANALYSIS

FILTER - Filters data based on criteria
Example: =FILTER(A1:C10, B1:B10>50)

SORT - Sorts data from range
Example: =SORT(A1:C10, 2, TRUE)

UNIQUE - Returns unique values
Example: =UNIQUE(A1:A100)

COUNTUNIQUE - Counts unique values
Example: =COUNTUNIQUE(A1:A100)

💼 REAL-WORLD SCENARIO: PROJECT MANAGEMENT TASK TRACKING

SCENARIO
Sarah is a project manager tracking team tasks in a spreadsheet. She needs to
monitor completion status, calculate completion rates, and identify overdue items.

SAMPLE DATA STRUCTURE
• Column A: Task Name
• Column B: Status (Complete, In Progress, Not Started)
• Column C: Due Date
• Column D: Priority (High, Medium, Low)
• Column E: Assigned To

PRACTICAL FORMULAS

1. Count Completed Tasks
=COUNTIF(B:B, "Complete")
Result: Returns the total number of completed tasks

2. Calculate Completion Percentage
=COUNTIF(B:B, "Complete") / COUNTA(B:B) * 100
Result: Shows what percentage of tasks are complete

3. Count High-Priority Incomplete Tasks
=COUNTIFS(B:B, "<>Complete", D:D, "High")
Result: Identifies how many high-priority tasks remain

4. Count Overdue Tasks
=COUNTIFS(B:B, "<>Complete", C:C, "<"&TODAY())
Result: Shows tasks that are past due and not complete

5. Count Tasks by Team Member
=COUNTIF(E:E, "John Smith")
Result: Shows total tasks assigned to John Smith

6. Sum of Completed High-Priority Tasks
=COUNTIFS(B:B, "Complete", D:D, "High")
Result: Tracks completed high-priority work

7. Average Days to Complete
=AVERAGEIF(B:B, "Complete", F:F)
(Assuming Column F contains completion time in days)

DASHBOARD SUMMARY EXAMPLE
Sarah creates a project dashboard with these formulas:

Metric: Total Tasks
Formula: =COUNTA(B2:B100)
Value: 50

Metric: Completed
Formula: =COUNTIF(B2:B100, "Complete")
Value: 35

Metric: In Progress
Formula: =COUNTIF(B2:B100, "In Progress")
Value: 10

Metric: Not Started
Formula: =COUNTIF(B2:B100, "Not Started")
Value: 5

Metric: Completion %
Formula: =COUNTIF(B2:B100, "Complete")/COUNTA(B2:B100)*100
Value: 70%

Metric: Overdue Tasks
Formula: =COUNTIFS(B2:B100, "<>Complete", C2:C100, "<"&TODAY())
Value: 3

Metric: High Priority Remaining
Formula: =COUNTIFS(B2:B100, "<>Complete", D2:D100, "High")
Value: 4

VISUAL STATUS INDICATOR
=IF(COUNTIFS(B:B, "<>Complete", C:C, "<"&TODAY()) > 5,
"⚠️ ACTION NEEDED",
"✅ On Track")

Result: Shows a visual alert if more than 5 tasks are overdue

🖼️ IMAGE DESCRIPTIONS (ALT TEXT EXAMPLES)

When adding images to your spreadsheet documentation, use descriptive alt text:

FORMULA BAR SCREENSHOT
Alt text: "Screenshot showing the Excel formula bar with the VLOOKUP formula
#ERROR!s!A:D,3,FALSE) entered, highlighting the cell reference
and formula syntax“

COUNTIF EXAMPLE
Alt text: "Spreadsheet example showing COUNTIF formula counting tasks marked
as 'Complete' in column B, with result showing 15 out of 25 total tasks completed“

DASHBOARD VISUAL
Alt text: "Project management dashboard showing key metrics: 70% completion rate,
3 overdue tasks displayed in red, and a pie chart breaking down tasks by status
(Complete: 35, In Progress: 10, Not Started: 5)“

VLOOKUP DIAGRAM
Alt text: "Diagram illustrating VLOOKUP function with color-coded arrows showing:
lookup value in blue, table range in green, column index in orange, and return
value highlighted in yellow“

DATE FUNCTION EXAMPLE
Alt text: "Calendar visualization showing NETWORKDAYS function calculating 15
workdays between May 1 and May 22, excluding weekends highlighted in gray and
two holiday dates marked with stars“