# Spreadsheets for Marketers: The 10 Formulas You Need

> The 10 Google Sheets formulas marketers use most, from SUMIFS and XLOOKUP to QUERY and REGEXEXTRACT, with GEO tracking examples.

Source: https://mentionx.ai/learn/foundations/spreadsheets-10-formulas-need  
Author: Dipuck Jones, founder of MentionX  
Updated: 30 October 2026  
Level: Beginner

**Ten Google Sheets formulas cover most of what marketers need: SUMIFS, COUNTIFS and AVERAGEIFS for conditional totals; XLOOKUP and IFERROR for combining sheets safely; UNIQUE, FILTER and QUERY for slicing data; and SPLIT and REGEXEXTRACT for cleaning text like URLs. Learn these and you can build an AI visibility tracker, a campaign report or a budget model without help.**

*Syntax and definitions are from Google's Sheets function list, checked 31 Oct 2026 (CET). All data examples are illustrative. "Our view" marks opinion.*

> **Key takeaways**
>
> - Ten formulas cover most marketing analysis: conditional totals, lookups, filtering and text handling.
> - Set up clean tables with one row per record before writing formulas.
> - A GEO tracker uses the same formulas to calculate mention rates by engine and intent.
> - Check formulas on a small sample before trusting a report.

*Checked on 31 October 2026. "Our view" marks MentionX opinion. Ledgerline is a fictional company used for illustration.*

## Why do marketers need spreadsheets?

Most marketing tools export CSVs. Search Console, GA4, ad platforms and AI visibility tools all let you download data, and the useful analysis often happens when you combine them. *Our view:* you don't need advanced skills, just a handful of reliable formulas and tidy data: one row per record, one column per field, headers in row 1.

The examples below use an fictional tracking sheet called `Runs` with columns: A Date, B Prompt, C Assistant, D Mentioned (TRUE/FALSE), E Cited URL, F Competitor mentioned.

## 1. SUMIFS: totals with conditions

`SUMIFS(sum_range, criteria_range1, criterion1, ...)` returns the sum of a range depending on multiple criteria ([Google](https://support.google.com/docs/table/25273?hl=en)).

*Example:* total clicks from organic search in October, in a sheet with channel in column B, month in C and clicks in D: `=SUMIFS(D:D, B:B, "Organic", C:C, "Oct")`.

## 2. COUNTIFS: counts with conditions

`COUNTIFS(criteria_range1, criterion1, ...)` returns the count of a range depending on multiple criteria ([Google](https://support.google.com/docs/table/25273?hl=en)).

*Example:* how many runs in Perplexity mentioned you: `=COUNTIFS(Runs!C:C, "Perplexity", Runs!D:D, TRUE)`. Divide by all Perplexity runs to get a mention rate: `=COUNTIFS(Runs!C:C,"Perplexity",Runs!D:D,TRUE)/COUNTIFS(Runs!C:C,"Perplexity")`.

## 3. AVERAGEIFS: averages with conditions

`AVERAGEIFS(average_range, criteria_range1, criterion1, ...)` returns the average of a range depending on multiple criteria ([Google](https://support.google.com/docs/table/25273?hl=en)).

*Example:* average position for brand queries from a Search Console export: `=AVERAGEIFS(E:E, F:F, "Brand")`. *Our view:* averages hide spread, so check a few rows by eye.

## 4. XLOOKUP: pull a value from another table

`XLOOKUP(search_key, lookup_range, result_range, missing_value, ...)` returns values from the result range based on where a match was found in the lookup range ([Google](https://support.google.com/docs/table/25273?hl=en)).

*Example:* look up the content owner for each cited URL from a `Pages` sheet: `=XLOOKUP(E2, Pages!A:A, Pages!B:B, "Not ours")`. The `missing_value` argument gives a label when there's no match.

## 5. IFERROR: show something useful instead of an error

`IFERROR(value, [value_if_error])` returns the first argument if it isn't an error, otherwise the second argument, or a blank if the second is absent ([Google](https://support.google.com/docs/table/25273?hl=en)).

*Example:* avoid `#DIV/0!` in a click-through rate: `=IFERROR(C2/B2, "")`.

## 6. UNIQUE: list distinct values

`UNIQUE(range)` returns unique rows, discarding duplicates, in the order they first appear ([Google](https://support.google.com/docs/table/25273?hl=en)).

*Example:* list every competitor that showed up in AI answers: `=UNIQUE(Runs!F2:F)`. Pair it with COUNTIFS next to it to count how often each appeared.

## 7. FILTER: show only rows that match

`FILTER(range, condition1, [condition2])` returns only rows or columns that meet the conditions ([Google](https://support.google.com/docs/table/25273?hl=en)).

*Example:* show all runs where you weren't mentioned but a competitor was: `=FILTER(Runs!A2:F, Runs!D2:D=FALSE, Runs!F2:F<>"")`. This is a quick gap list.

## 8. QUERY: SQL-like summaries

`QUERY(data, query, [headers])` runs a Google Visualization API Query Language query across data ([Google](https://support.google.com/docs/table/25273?hl=en)).

*Example:* mentions by assistant in one formula: `=QUERY(Runs!A1:F, "select C, count(B) where D = TRUE group by C", 1)`. *Our view:* QUERY is the most powerful formula here but also the easiest to break; keep the query text simple and documented.

## 9. SPLIT: break text into columns

`SPLIT(text, delimiter, ...)` divides text around a character or string and puts each fragment in a separate cell ([Google](https://support.google.com/docs/table/25273?hl=en)).

*Example:* split a UTM-tagged campaign name like `geo_q4_launch` into parts: `=SPLIT(A2, "_")`.

## 10. REGEXEXTRACT: pull patterns out of text

`REGEXEXTRACT(text, regular_expression)` extracts matching substrings according to a regular expression ([Google](https://support.google.com/docs/table/25273?hl=en)).

*Example:* get the domain from a cited URL: `=REGEXEXTRACT(E2, "https?://([^/]+)")`. Then COUNTIFS on the domain column shows which sites AI assistants cite most.

## How do these fit together in a GEO tracker?

*Fictional build:*

| Tab | Formulas used | Output |
|---|---|---|
| Runs | REGEXEXTRACT for domain | Raw run log |
| Summary | COUNTIFS, IFERROR | Mention rate per assistant |
| Competitors | UNIQUE, COUNTIFS | Share of mentions per brand |
| Gaps | FILTER | Prompts where competitors appear and you don't |
| Sources | UNIQUE, COUNTIFS, XLOOKUP | Most-cited domains, and whether they're yours |

See the [citation gap analysis template](https://mentionx.ai/learn/templates/citation-gap-analysis-template) and the [executive GEO dashboard](https://mentionx.ai/learn/templates/executive-geo-dashboard-1-page-kpi) for ready-made layouts.

## How should you set up a sheet before using formulas?

*Our view:* a few habits save hours. Keep raw exports on their own tabs and never edit them by hand, so you can paste a fresh export over them. Put all calculations on separate tabs that reference the raw data. Keep assumptions, such as a target mention rate or a cost per run, in one clearly labelled inputs area. Name tabs plainly (Runs, Summary, Gaps) so anyone can follow the logic.

## What breaks spreadsheets?

- **Merged cells and blank header rows.** They break ranges and QUERY.
- **Mixing text and numbers** in one column, such as "12%" typed as text.
- **Hard-coding numbers inside formulas** instead of referencing an inputs cell.
- **No date column.** Without dates, you can't see trends.

## Where next?

Read [UTM parameters and attribution basics](https://mentionx.ai/learn/foundations/utm-parameters-attribution-basics) and Google Analytics 4 basics.

## From MentionX data: a tracker built on these formulas

**Method.** Read-only aggregate queries over MentionX scans from 22 August to 29 October 2026 (UTC): 2,217 engine runs (2,154 completed) across 18 anonymised projects and six engines.

Tables like these are what a GEO spreadsheet produces. In the MentionX sample, mention rates by engine were:

| Engine | Brand mentioned | Answers with no citations |
| --- | --- | --- |
| Gemini | 47.3% | 50.7% |
| OpenAI | 42.9% | 30.2% |
| Perplexity | 31.5% | 4.1% |
| AI Mode | 31.3% | 12.6% |
| Copilot | 26.0% | 47.0% |
| DeepSeek | 24.0% | 99.0% |

*Our view:* a COUNTIFS over a results sheet, divided by a COUNTIFS of runs, gives exactly this kind of mention rate. Building it yourself helps you understand what the number means.

**Limits.** A convenience sample of mostly B2B software projects, run through engine APIs, which can behave differently from the consumer apps. Perplexity, Copilot and DeepSeek figures come from a single day (26 October 2026), so treat them as a snapshot, not a trend.

## Worked example: Ledgerline builds a prompt tracker

*Ledgerline is a fictional accounts-payable tool. Figures are illustrative.*

Ledgerline's marketing analyst keeps one row per engine run: date, engine, prompt, intent, brand mentioned (yes or no) and cited URL.

She uses COUNTIFS to count mentions by engine and intent, divides by total runs for a rate, and uses XLOOKUP to pull each prompt's funnel stage from a separate tab. UNIQUE lists the cited domains, and REGEXEXTRACT pulls the domain from each cited URL. A FILTER view shows only runs where competitor A was named and Ledgerline wasn't. All data here is fictional.

> **Pro move:** Keep raw data on one tab and calculations on another. Never type over raw data.

## Which formulas should you learn first?

SUMIFS, COUNTIFS and XLOOKUP. Together they cover most reporting. Add FILTER and UNIQUE next, then text functions when you need to clean data.

## How do you make sheets others can use?

Label columns clearly, freeze the header row, add a short notes tab explaining sources and formulas, and colour input cells differently from calculated cells. *Our view:* a sheet only you understand is a risk.

## How do you check a formula is right?

Test it on a handful of rows you can count by hand. Check edge cases, such as blanks and duplicates. If the result looks surprising, investigate before sharing.

## When should you move beyond spreadsheets?

When data grows to many thousands of rows, needs several people editing at once or must refresh automatically, consider a database or BI tool. Spreadsheets remain great for exploring and prototyping.

## How do you handle dates?

Store dates as real dates, not text, so you can filter and group by month. Use a consistent time zone for timestamps.

## How do you share results?

Share a summary tab or chart, not the raw sheet. Include the date range and sample size so readers know what they're looking at.

## How do you combine formulas?

Most useful reports combine two or three formulas. A mention rate is COUNTIFS of mentioned runs divided by COUNTIFS of all runs for the same engine and month, wrapped in IFERROR so empty months show a blank instead of an error. A lookup inside a filter can pull labels into a filtered view. Build combined formulas step by step in helper columns, check each step, then merge them if you want a tidier sheet.

## How do you name ranges and tables?

Named ranges, such as Runs or Prompts, make formulas easier to read than cell references like A2:F5000. In Excel, formatting data as a table gives structured references automatically. In Google Sheets, named ranges do the same job. *Our view:* readable formulas are easier to check and to hand over.

## How do you clean messy data?

Use TRIM to remove stray spaces, LOWER or UPPER to standardise case, and SPLIT or REGEXEXTRACT to separate combined fields. Remove duplicates carefully, after checking they really are duplicates. Keep a copy of the raw data before cleaning, so you can start again if something goes wrong.

## How do you build a simple chart?

Summarise first: one row per month and one column per engine. Then insert a line or bar chart from that summary. Label the axes and title the chart with what it shows, such as "Mention rate by engine, monthly". Avoid charting raw rows.

## How do you protect a shared sheet?

Protect formula ranges so collaborators can't overwrite them by accident, and keep edit access to the people who need it. Use version history to recover from mistakes. Don't change sharing settings to public for convenience.

## How do you document a sheet for handover?

Add a notes tab listing each tab's purpose, where the data comes from, how often it's updated and what each key formula does. A new colleague should be able to update the report without asking you.

## How do you practise these formulas?

Download a small export from your analytics or prompt tracker and rebuild one existing report from scratch using these formulas. Compare your numbers with the original. Any difference teaches you something about the data or the formula.

## Common questions

**Do these work in Excel and Google Sheets?** Most do. QUERY and some regex functions are specific to Google Sheets.

**What's the most useful single formula?** COUNTIFS, because so much marketing reporting is counting things that meet conditions.

**Should I use pivot tables instead?** Pivot tables are great for exploring. Formulas are better for fixed reports.

**How do I avoid broken lookups?** Use exact matches and clean the lookup keys, such as trimming spaces.

## Sources

- Google Docs Editors Help, [Google Sheets function list](https://support.google.com/docs/table/25273?hl=en)

## Frequently asked questions

### Which spreadsheet formula should a marketer learn first?

SUMIFS and COUNTIFS. They answer most reporting questions, such as clicks by channel or mentions by AI assistant.

### Do these formulas work in Excel?

Most have Excel equivalents with similar names. This guide uses Google Sheets syntax from Google's function list. QUERY and REGEXEXTRACT are Sheets-specific.

### What is the safest way to handle errors in a sheet?

Wrap lookups in IFERROR so a missing value shows a blank or a label instead of an error.

---

Written and reviewed by Dipuck Jones for MentionX Learn. The HTML version of this page: https://mentionx.ai/learn/foundations/spreadsheets-10-formulas-need