Spreadsheets for Marketers: The 10 Formulas You Need

#01Why 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.

#021. SUMIFS: totals with conditions

SUMIFS(sum_range, criteria_range1, criterion1, ...) returns the sum of a range depending on multiple criteria (Google).

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").

#032. COUNTIFS: counts with conditions

COUNTIFS(criteria_range1, criterion1, ...) returns the count of a range depending on multiple criteria (Google).

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").

#043. AVERAGEIFS: averages with conditions

AVERAGEIFS(average_range, criteria_range1, criterion1, ...) returns the average of a range depending on multiple criteria (Google).

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.

#054. 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).

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.

#065. 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).

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

#076. UNIQUE: list distinct values

UNIQUE(range) returns unique rows, discarding duplicates, in the order they first appear (Google).

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.

#087. FILTER: show only rows that match

FILTER(range, condition1, [condition2]) returns only rows or columns that meet the conditions (Google).

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.

#098. QUERY: SQL-like summaries

QUERY(data, query, [headers]) runs a Google Visualization API Query Language query across data (Google).

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.

#109. SPLIT: break text into columns

SPLIT(text, delimiter, ...) divides text around a character or string and puts each fragment in a separate cell (Google).

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

#1110. REGEXEXTRACT: pull patterns out of text

REGEXEXTRACT(text, regular_expression) extracts matching substrings according to a regular expression (Google).

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

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

Fictional build:

See the citation gap analysis template and the executive GEO dashboard for ready-made layouts.

#13How 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.

#14What 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.

#15Where next?

Read UTM parameters and attribution basics and Google Analytics 4 basics.

#16From MentionX data: a tracker built on these formulasOriginal data

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:

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.

#17Worked 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.

#18Which 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.

#19How 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.

#20How 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.

#21When 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.

#22How 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.

#23How 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.

#24How 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.

#25How 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.

#26How 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.

#27How 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.

#28How 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.

#29How 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.

#30How 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.

#31Common 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.