Google Sheets

Google Sheets formulas every eBay × AliExpress tracker should know

July 13, 2026 · 8 min read

Key takeaways

SUMIFS and COUNTIFS are the workhorses for daily and monthly profit totals, and they update instantly as new rows get written.
ARRAYFORMULA turns a formula written once into one that fills itself down every new row, so you stop dragging fill handles after every sync.
QUERY handles anything SUMIFS cannot, particularly grouped reports like profit by tag or by SKU in one formula instead of a pivot table you have to refresh.
Wrapping formulas in IFERROR keeps a mostly-empty sheet from filling with error text before the first real orders land.
Fetch Order Tracking writes into a fixed, predictable column layout specifically so formulas like these keep working after every sync, not just once.

A tracking sheet with columns full of raw data is only half the job. The other half is the handful of formulas that turn those columns into answers: what did we make this week, how many orders are stuck, which tag is actually profitable. Here are the ones worth having in every eBay × AliExpress tracking sheet.

These assume a layout close to the one most trackers use: a date column, an Order Earning column, an AE Order Amount column, and a Tag or SKU column somewhere in the row. Adjust the column letters to match your own sheet.

SUMIFS for date-ranged profit totals

The single most useful formula in any tracking sheet is a running profit total for a specific period, and SUMIFS handles it cleanly without touching your data columns:

=SUMIFS(G:G,B:B,">="&DATE(2026,7,1),B:B,"<="&DATE(2026,7,31))-SUMIFS(J:J,B:B,">="&DATE(2026,7,1),B:B,"<="&DATE(2026,7,31))

This sums Order Earning minus AE Order Amount for every row whose date falls in July, giving a real profit figure for the month in one cell, with no manual filtering required.

ARRAYFORMULA so you stop dragging fill handles

Any formula written in a single cell, like a profit-per-row column, normally needs dragging down every time new rows get added by a sync. ARRAYFORMULA fixes this by applying the calculation to the entire column at once, so new rows fill themselves in the moment data lands in them:

=ARRAYFORMULA(IF(B2:B="","",G2:G-J2:J))

Placed once in row 2 of a Profit column, this calculates Order Earning minus AE Order Amount for every row that has a date, and stays blank for rows that do not, without you ever touching it again after a sync adds new orders.

QUERY for grouped reports a pivot table would normally handle

When you need a breakdown, profit by tag, order count by carrier, average order value by month, QUERY is the formula that replaces a pivot table you would otherwise have to manually refresh:

=QUERY(A2:M,"select F, sum(G)-sum(J), count(A) where B is not null group by F order by sum(G)-sum(J) desc label sum(G)-sum(J) 'Profit', count(A) 'Orders'")

This groups every row by the Tag column, sums profit per tag, counts orders per tag, and sorts the most profitable tag to the top, all in a single formula that recalculates live as new rows arrive.

COUNTIFS for a stuck-orders early warning

Orders that have sat in "Awaiting Dispatch" for too long are the ones most likely to become a problem before you notice. A simple COUNTIFS against your status column and a date cutoff turns that into a number you check at a glance instead of scrolling for it:

=COUNTIFS(P:P,"Awating Dispatch",B:B,"<="&TODAY()-5)

This counts every order still marked as awaiting dispatch that was placed more than five days ago, a useful proxy for orders worth checking on manually before they become a late-shipment problem.

IFERROR so an empty sheet does not look broken

Every formula above will show an error, usually #DIV/0! or #N/A, on a sheet that has no matching rows yet, which is exactly the state a brand-new sheet starts in. Wrap any formula that might run against zero matching rows in IFERROR so it shows a clean value instead of alarming error text:

=IFERROR(SUMIFS(G:G,B:B,">="&DATE(2026,7,1)),0)

This is a small habit, but it is the difference between a sheet that looks trustworthy from the first order and one that looks broken until enough data accumulates to stop the errors on its own.

All of these formulas depend on one thing holding true: your data columns staying in the same position every time new orders get written. Fetch Order Tracking writes into a fixed column layout on every sync specifically so formulas like these keep working indefinitely, instead of breaking the first time a column shifts. See how it works.

Frequently asked questions

What is the best formula for a running profit total in a tracking sheet?

SUMIFS against your Order Earning and AE Order Amount columns, filtered by a date range, gives a live profit total for any period without manual filtering. It recalculates automatically as new rows are added.

How do I stop having to drag a formula down every time new orders sync?

Wrap the formula in ARRAYFORMULA and place it once in the first data row. It applies the calculation to the entire column automatically, so new rows added by a sync are calculated the moment they contain data, with no manual dragging required.

Can I build a profit-by-tag report without a pivot table?

Yes. A QUERY formula with a group by clause on your Tag column, summing profit and counting orders, produces the same breakdown a pivot table would, but it updates live rather than requiring a manual refresh.

Related guides

Try Fetch Order Tracking → More guides