A GuideHollow publicationExplore the AI guides ↗
GuideHollow Sheets.
Search

Find a starting point

Formula Finder

Eight everyday functions. Choose your spreadsheet app, find your task, and adapt a clear example.

This is a curated reference, not a calculator. Examples and expected results are static. No formulas run in Excel or Google Sheets here. English function names and comma separators are shown; your locale may require different names or semicolons.

8 formulas. Showing Excel examples.

Sum and count

SUMIFS

Add amounts that meet every condition.

Excel syntax

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Example to copy

=SUMIFS(C2:C4,A2:A4,"East",B2:B4,"Paid")

Sample inputs

A2:C4 rows: East | Paid | 20; West | Paid | 30; East | Paid | 15. Amounts in C are numbers.

Expected output

35

Limits and compatibility

Conditions use AND. Keep every range the same size. Put sum_range first; SUMIF uses a different argument order. Text and operator criteria need quotes.

Official documentation: Microsoft SUMIFS · Google Sheets SUMIFS

Sum and count

COUNTIFS

Count rows that satisfy multiple conditions.

Excel syntax

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)

Example to copy

=COUNTIFS(A2:A4,"East",B2:B4,">=10")

Sample inputs

A2:B4 rows: East | 20; West | 30; East | 5. Column B contains numbers.

Expected output

1

Limits and compatibility

Each row must meet every condition. Use ranges of equal dimensions. Wildcards in text criteria can match more than literal text; use ~ to escape * or ?.

Official documentation: Microsoft COUNTIFS · Google Sheets COUNTIFS

Look up data

XLOOKUP

Return the value beside a matching identifier.

Excel syntax

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Example to copy

=XLOOKUP("P02",A2:A4,B2:B4,"Not found",0)

Sample inputs

A2:B4 rows: P01 | Pen; P02 | Notebook; P03 | Clip.

Expected output

Notebook

Limits and compatibility

This example explicitly uses exact matching (0) and returns the first match. Excel 2016/2019 do not support XLOOKUP; use Microsoft 365, Excel 2021 or newer. Binary search modes require correctly sorted data. Text and numeric keys can differ.

Official documentation: Microsoft XLOOKUP · Google Sheets XLOOKUP

Filter and deduplicate

FILTER

Return rows meeting a condition.

Excel syntax

FILTER(array, include, [if_empty])

Example to copy

=FILTER(A2:B4,B2:B4>=10,"No matches")

Sample inputs

A2:B4 rows: Pen | 8; Book | 15; Clip | 12.

Expected output

Two rows: Book | 15; Clip | 12.

Limits and compatibility

Excel requires a dynamic-array version such as Microsoft 365 or Excel 2021+. Its third argument is an empty-result fallback. In Sheets, additional arguments are conditions, not a fallback; no match returns #N/A. Conditions must align with the range. Leave output cells clear for the returned array.

Official documentation: Microsoft FILTER · Google Sheets FILTER

Handle errors

IFERROR

Show a helpful fallback when a calculation returns an error.

Excel syntax

IFERROR(value, value_if_error)

Example to copy

=IFERROR(A2/B2,"Check divisor")

Sample inputs

A2 = 10; B2 = 0.

Expected output

Check divisor

Limits and compatibility

Catches many error types and may hide a broken reference or bad input. Diagnose the underlying formula first. Sheets permits an omitted fallback (blank); Excel requires the second argument.

Official documentation: Microsoft IFERROR · Google Sheets IFERROR

Clean text

TRIM

Remove extra ordinary spaces from text.

Excel syntax

TRIM(text)

Example to copy

=TRIM(A2)

Sample inputs

A2 contains " Ada Lovelace " (ordinary ASCII spaces).

Expected output

Ada Lovelace

Limits and compatibility

Also reduces repeated ordinary spaces between words to one. Nonbreaking spaces and other Unicode whitespace need separate cleaning. Do not apply to identifiers where spaces matter.

Official documentation: Microsoft TRIM · Google Sheets TRIM

Filter and deduplicate

UNIQUE

Make a distinct list from repeated values.

Excel syntax

UNIQUE(array, [by_col], [exactly_once])

Example to copy

=UNIQUE(A2:A5)

Sample inputs

A2:A5 contains, in order: Pen; Book; Pen; Clip.

Expected output

Three rows: Pen; Book; Clip.

Limits and compatibility

Returns an array, so leave output cells clear. Excel requires Microsoft 365 or Excel 2021+. The default returns distinct values, not only values appearing once; exactly_once changes that behavior. Hidden spaces can create apparently repeated text.

Official documentation: Microsoft UNIQUE · Google Sheets UNIQUE

Clean text

LEFT

Extract a fixed-length prefix from a text value.

Excel syntax

LEFT(text, [num_chars])

Example to copy

=LEFT(A2,3)

Sample inputs

A2 contains text "INV-2048".

Expected output

INV

Limits and compatibility

The character count must be nonnegative. This is a fixed-position extraction, not a delimiter parser. Format identifier inputs as text to keep leading zeros. Unicode character counting can depend on the spreadsheet product and workbook compatibility.

Official documentation: Microsoft LEFT · Google Sheets LEFT

Before you use an example

Replace sample ranges with your own cells, keep matching ranges aligned, and test on a small copy of your data. Text such as P02 is an identifier, while the amount examples use actual numeric cells. A correct formula can still produce the wrong answer if your source data has hidden spaces, text-formatted numbers or unexpected blanks.

How these examples are checked

Syntax and compatibility notes were reviewed against the linked official documentation on October 24, 2026. Automated JavaScript checks cover the simple arithmetic, filtering and text results in these sample fixtures; they do not emulate a spreadsheet engine or verify behavior in a live workbook. Verify results in your target app and version.