You do not need hundreds of formulas to be effective in Google Sheets. A dozen well-understood functions cover most business reporting. Here they are, grouped by the kind of question they answer.
Totals and averages
SUM
=SUM(D2:D500) adds a range. Use it for revenue, costs or units.
AVERAGE
=AVERAGE(D2:D500) returns the mean. Be careful: a few very large orders can pull the average up. Consider MEDIAN alongside it.
COUNT and COUNTA
COUNT counts numbers; COUNTA counts any non-empty cell. Use COUNTA for order IDs that include letters.
Conditions
IF
=IF(E2>=100,"Large order","Standard") labels each row. Useful for creating categories you can filter on.
SUMIF and SUMIFS
=SUMIF(B:B,"Email",D:D) totals revenue for the Email channel. SUMIFS handles several conditions, such as channel and month.
COUNTIF and COUNTIFS
=COUNTIF(C:C,"Refunded") counts refunded orders. Pair it with a total to get a refund rate.
Looking things up
XLOOKUP (and VLOOKUP)
=XLOOKUP(A2,Products!A:A,Products!C:C) pulls a product's cost from another sheet. XLOOKUP is more flexible than VLOOKUP because the return column can be anywhere.
Cleaning text
TRIM, PROPER and SPLIT
=TRIM(A2) removes extra spaces that break lookups. =PROPER(A2) fixes capitalization. =SPLIT(A2,",") separates values into columns.
Working with dates
TEXT and EOMONTH
=TEXT(A2,"yyyy-mm") creates a month label for grouping. =EOMONTH(A2,0) returns the last day of the month.
A Sheets-only power tool: QUERY
=QUERY(A1:F500,"select B, sum(F) group by B order by sum(F) desc",1) summarizes revenue by category in one formula. It uses a language similar to SQL, which makes it a gentle bridge to Week 2 of our course.
Putting them together
A simple weekly marketing summary might combine SUMIFS for spend by channel, COUNTIFS for conversions and a division to get cost per conversion. Wrap divisions in IFERROR so empty weeks show a dash instead of an error.
Learn the formulas that answer your most frequent questions first. Breadth can come later.
For the Excel version of a full workflow, read how to analyze sales data in Excel.
