Five Formulas That Run Your Reports
You do not need to learn a hundred formulas. You need five.
These five formulas will cover 90% of what you want to track. Total sales. Number of orders. Average deal size. Complaints by category. Performance by salesperson. All of it comes down to counting and adding, with or without conditions.
If you have used Excel, you already know most of these. The syntax in Google Sheets is nearly identical.
1. SUM
Adds up numbers in a range.
=SUM(D2:D100)This adds everything in column D from row 2 to row 100.
When you use it: Total sales amount. Total expenses. Total quantity dispatched. Anything where you need a grand total.
Practical tip: Instead of writing D2:D100, write D2:D. This means "from D2 to the end of column D." Now when new form responses come in and land in row 101, 102, 103, they automatically get included. You do not have to update the formula.
=SUM(D2:D)2. COUNT and COUNTA
COUNT counts cells that contain numbers. COUNTA counts cells that contain anything (numbers, text, dates).
=COUNT(D2:D)
=COUNTA(A2:A)When you use it: How many orders came in. How many feedback forms were submitted. How many entries exist in your data.
Which one to pick: If the column contains numbers (like amounts), use COUNT. If it contains text (like names or dates), use COUNTA.
For form responses, the timestamp column is reliable. Every submission has a timestamp. So:
=COUNTA(A2:A)This tells you exactly how many responses you have received.
3. COUNTIF
Counts only the rows that match a condition.
=COUNTIF(C2:C, "Delhi")This counts how many times "Delhi" appears in column C.
=COUNTIF(E2:E, ">10000")This counts how many values in column E are greater than 10,000.
When you use it: Orders from a specific city. Complaints of a specific type. Ratings below 3. Deals above a certain value. Any time you want to count "how many of these."
Common conditions:
"Delhi"— exactly matches Delhi">10000"— greater than 10,000"<5"— less than 5">=3"— greater than or equal to 3"<>"— not empty
4. SUMIF
Adds up values, but only for rows that match a condition.
=SUMIF(B2:B, "Manish", D2:D)This adds up all values in column D, but only where column B says "Manish."
In plain English: What are the total sales made by Manish?
=SUMIF(C2:C, "Mumbai", D2:D)Total sales from Mumbai.
=SUMIF(E2:E, ">5000", E2:E)Total of all orders above 5,000.
When you use it: Sales by salesperson. Revenue by city. Expenses by category. Collection by month. Any time you want totals broken down by something.
5. AVERAGE
Calculates the average of a range.
=AVERAGE(D2:D)There is also AVERAGEIF, which works like SUMIF:
=AVERAGEIF(B2:B, "Manish", D2:D)Average order value for Manish's sales.
When you use it: Average order size. Average feedback rating. Average time to close a deal. Any time you need to understand "typical" rather than "total."
Putting it together
Let us say you have a sales form. Every time a sale happens, someone fills it. The data lands in a sheet with these columns:
| A | B | C | D | E |
|---|---|---|---|---|
| Timestamp | Salesperson | City | Amount | Product |
You create a new tab called "Dashboard." In that tab:
Total Sales
=SUM(D2:D)Number of Orders
=COUNTA(A2:A)Average Order Value
=AVERAGE(D2:D)Orders This Week
=COUNTIF(A2:A, ">="&TODAY()-7)Sales by Manish
=SUMIF(B2:B, "Manish", D2:D)Orders from Delhi
=COUNTIF(C2:C, "Delhi")Average Order Value in Mumbai
=AVERAGEIF(C2:C, "Mumbai", D2:D)You write these formulas once. After that, every form submission updates every number automatically.
The date trick
You will often want to filter by time. This month. Last 7 days. This quarter.
Google Sheets gives you functions to work with dates:
TODAY()— today's dateTODAY()-7— 7 days agoEOMONTH(TODAY(),0)— end of current monthEOMONTH(TODAY(),-1)+1— first day of current month
Orders in the last 7 days:
=COUNTIF(A2:A, ">="&TODAY()-7)Sales this month:
=SUMIFS(D2:D, A2:A, ">="&EOMONTH(TODAY(),-1)+1, A2:A, "<="&TODAY())Note: SUMIFS (with an S) lets you apply multiple conditions. Here we are saying: sum column D where the date is greater than or equal to the first of this month AND less than or equal to today.
What you do not need right now
You might have heard of VLOOKUP, QUERY, FILTER, IMPORTRANGE, and other advanced functions. They are useful, but not essential for your first self-updating reports.
Master these five first. Build a few dashboards. See them work. The advanced functions solve problems you have not encountered yet. When you encounter them, you will learn those functions. Not before.
One warning
Formulas work on clean data. If your form responses have inconsistent entries, your formulas will give wrong answers.
If someone types "delhi" and someone else types "Delhi" and a third person types "New Delhi", COUNTIF sees three different cities.
This is why form design matters. Use dropdowns instead of text fields. Use response validation. The cleaner your data entry, the more reliable your reports.
We covered this in the forms post. If you skipped it, go back and read it.
Your task
Open the Google Sheet connected to one of your forms.
Create a new tab. Call it "Summary."
Write three formulas:
- Total count of responses
- Sum of one numeric column
- One COUNTIF or SUMIF that breaks down data by a category
Watch it update the next time someone submits the form.
That is your first self-updating report.