Essential Cnshopper Spreadsheet Formulas
A cnshopper spreadsheet without formulas is just a list. Formulas transform that list into a living dashboard that calculates, filters, warns, and summarizes automatically. You do not need to be a spreadsheet wizard — these 15 formulas cover 95% of everything a shopper needs. Copy them directly into your sheet, adjust the column letters to match your layout, and watch your workflow accelerate.
The Foundation: SUM and SUMIF
Every tracking spreadsheet needs totals. The basic SUM formula adds everything in a range: =SUM(C2:C100). But real shopping data is categorized — you want to know total spending on sneakers, not just total spending overall. That is where SUMIF comes in.
| Formula | What It Does | Example Use |
|---|---|---|
| =SUM(C2:C100) | Total of all values in range | Total spending across all orders |
| =SUMIF(B2:B100,"Shoes",C2:C100) | Sum only where category is Shoes | Total spent on footwear only |
| =SUMIF(D2:D100,"Delivered",C2:C100) | Sum only delivered orders | Money actually received in goods |
| =COUNTIF(E2:E100,"Pending") | Count rows matching criteria | How many orders are pending |
SUMIF has three parts: the range to check, the criteria to match, and the range to sum. Master this one formula and you can generate category breakdowns, status summaries, and monthly spending totals without manual calculation.
Profit and Margin Calculators
Resellers live and die by margins. These formulas turn raw price data into actionable business intelligence. Place them in dedicated columns so every row shows instant profitability.
| Formula | What It Does | Example Use |
|---|---|---|
| =C2-B2-D2 | Net profit per item | Sell - Buy - Fees |
| =(C2-B2)/B2 | Profit margin percentage | 25% means 25 cents profit per dollar |
| =IFERROR(C2-B2-D2,"N/A") | Safe profit with error handling | Shows "N/A" if data is missing |
| =IF(C2>B2*1.3,"Good","Skip") | Auto-rates sourcing opportunities | Flags items with 30%+ margin |
The IF formula for sourcing opportunities is a hidden gem. Set your minimum margin threshold (1.3 for 30%), and the formula instantly labels every item as "Good" or "Skip." Sort by this column before placing bulk orders and you will never accidentally source low-margin inventory again.
Date and Timeline Formulas
Timing is everything in reselling and group buying. These date formulas track how long orders take, identify delays, and predict future delivery windows based on historical averages.
Days Active
Days since order date. Use this to see how long an order has been active.
Est. Delivery
Estimated delivery date based on 14-day shipping average. Adjust the number to match your carrier.
Shipping Duration
Exact days between two dates. Perfect for measuring actual shipping duration.
Week Grouping
Groups orders by the week they were placed. Use with SUMIF for weekly spending analysis.
Combine WEEKNUM with SUMIF for powerful weekly reports. In a separate dashboard tab, create a table where each row is a week number, and the value column uses =SUMIF(OrderWeekColumn,WeekNumber,PriceColumn). You now have a weekly spending trend chart with zero manual data entry.
Download the Formula Cheat Sheet
Get a printable PDF with all 15 formulas, copy-paste ready, with explanations for when to use each one.
Download Cheat SheetConditional Formatting Rules
Formulas calculate values. Conditional formatting makes those values visible. These rules turn a boring grid into a color-coded dashboard where problems jump off the screen.
| Rule | Formula | Visual |
|---|---|---|
| Late Orders | =TODAY()-ShipDate>ExpectedDays | Light red background |
| High Margin | =ProfitColumn>50 | Green text, bold |
| Pending Too Long | =AND(Status="Pending",Days>7) | Yellow background |
| Duplicate Check | =COUNTIF(ProductColumn,ProductCell)>1 | Orange border |
Apply these rules to entire columns, not individual cells. Select the column, open Format > Conditional Formatting, and paste the custom formula. From that moment on, every new row you add inherits the formatting automatically. The spreadsheet becomes self-organizing.
Frequently Asked Questions
SUMIF is the single most powerful formula. It lets you sum spending by category (shoes, apparel, accessories) or by status (delivered, pending, cancelled). One formula replaces manual addition across hundreds of rows.
Master these formulas and your spreadsheet becomes a self-updating command center.
Build Your Sheet