Recommended Free Tools
For common small-business spreadsheet tasks, start with SUM to total values, SUMIF and SUMIFS to total by criteria, COUNTIF and COUNTIFS to count matching records, and XLOOKUP to retrieve related information. The ten functions below are a practical selection for routine tracking—not a ranking of which functions businesses use most.
Choose a function by the job
| What you need to do | Start with | Example |
|---|---|---|
| Total numbers | SUM |
Add expense amounts in a range |
| Total values matching one condition | SUMIF |
Add sales for one product |
| Total values matching several conditions | SUMIFS |
Add a product’s sales within a date range |
| Return one of two results based on a test | IF |
Label an invoice overdue or not overdue |
| Count matches to one condition | COUNTIF |
Count invoices marked Unpaid |
| Count matches to several conditions | COUNTIFS |
Count unpaid invoices for a customer |
| Retrieve a related value | XLOOKUP |
Return a product price from its SKU |
| Substitute a result when a formula errors | IFERROR |
Display a message when a lookup fails |
| Count nonempty cells | COUNTA |
Count records with a populated required field |
| Find the arithmetic mean | AVERAGE |
Calculate average order value |
1. SUM: add a range
SUM adds values in cells. If invoice line totals are in cells E2 through E100, use:
As an Amazon Associate I earn from qualifying purchases.
=SUM(E2:E100)
Replace the range with the cells that hold the amounts you want to total. Microsoft’s SUM function reference describes the function and its use.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minute2. SUMIF: total values that meet one condition
Use SUMIF when one criterion determines which values to add. For example, if product names are in column A and sales amounts in column B, this totals sales for a product named “Notebook”:
#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
=SUMIF(A2:A100,"Notebook",B2:B100)
The syntax is SUMIF(range, criteria, [sum_range]): Excel checks range against the criterion and adds corresponding cells in sum_range. Microsoft notes that criteria can be numbers, expressions, references, text, or functions. For example, to total amounts in B2:B100 where A2:A100 contains “Notebook,” the product column is the criteria range and the amount column is the sum range.
Microsoft warns that SUMIF can return incorrect results when matching strings exceed 255 characters or when matching the string #VALUE!. See Microsoft’s SUMIF function reference for syntax and limitations.
3. SUMIFS: total values that meet multiple conditions
Use SUMIFS when a total depends on more than one criterion. If column A contains product names, column B contains dates, and column C contains sales amounts, this totals Notebook sales from January 1 through January 31, 2026:
=SUMIFS(C2:C100,A2:A100,"Notebook",B2:B100,">="&DATE(2026,1,1),B2:B100,"<"&DATE(2026,2,1))
The date conditions include the first day and exclude the first day of the following month, avoiding ambiguity about entries with times. The syntax starts with the range to sum, followed by pairs of criteria ranges and criteria: SUMIFS(sum_range, criteria_range1, criteria1, ...). Each condition must be met for a row to contribute. Microsoft’s SUMIFS function reference documents the arguments.
4. IF: return a result based on a test
IF checks a logical condition and returns one result when it is true and another when it is false. For an invoice list with due dates in column B, payment status in column C, and a status label in D2, use:
=IF(AND(C2<>"Paid",B2<TODAY()),"Overdue","Not overdue")
Here, an invoice is labeled “Overdue” only when it is not marked Paid and its due date is before today. Adjust the test and labels to fit the values in your own sheet. Microsoft’s IF function reference shows conditional labels and calculations.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →5. COUNTIF: count records matching one condition
COUNTIF counts cells in a range that meet a criterion. If invoice statuses are in C2:C100, count those marked Unpaid with:
=COUNTIF(C2:C100,"Unpaid")
The status text in the formula must match the entries in the sheet. Microsoft lists COUNTIF in its Excel functions reference.
6. COUNTIFS: count records matching multiple conditions
Use COUNTIFS when a record must meet several criteria to be counted. To count unpaid invoices for a customer named “Acme” when customer names are in column A and statuses are in column C, use:
=COUNTIFS(A2:A100,"Acme",C2:C100,"Unpaid")
For a count limited to a date period, add the appropriate date range and criteria as another pair. Microsoft includes COUNTIFS in its Excel functions reference.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →7. XLOOKUP: retrieve information using an ID
XLOOKUP searches one range for a value and returns a corresponding item from another range. If product SKUs are in A2:A100 and prices are in C2:C100, look up the SKU in E2 with:
Rank #4
=XLOOKUP(E2,A2:A100,C2:C100,"SKU not found")
By default, XLOOKUP uses an exact match. Unlike older lookup patterns that impose a direction, it can return a value from a column to the left or right of the lookup column. The optional fourth argument provides a readable result when no SKU matches. Microsoft says XLOOKUP is unavailable in Excel 2016 and Excel 2019; confirm that everyone opening a shared workbook has a compatible version. See the XLOOKUP function reference.
8. IFERROR: provide a fallback for errors
IFERROR returns a specified value if its first argument evaluates to an error. To show “SKU not found” when a lookup fails, wrap the formula like this:
=IFERROR(XLOOKUP(E2,A2:A100,C2:C100),"SKU not found")
Use a fallback that makes the outcome clear, and investigate errors rather than automatically hiding them: the error may point to a misspelled reference, an unexpected input, or a broken formula. Microsoft explains the function in its IFERROR function reference.
9. COUNTA: count nonempty cells
COUNTA counts nonempty cells, including entries that are text. If each invoice row has a required invoice number in column A, use:
=COUNTA(A2:A100)
This counts populated invoice-number cells in the chosen range. It is only a reliable record count if that field is consistently filled for each record. Microsoft distinguishes COUNTA from COUNT, which counts numbers, in its Excel functions reference.
10. AVERAGE: calculate a mean
AVERAGE calculates the arithmetic mean of numeric values. If order totals are in D2:D100, use:
Free tools Windows power users keep installed
One-click scans. No signup required.
=AVERAGE(D2:D100)
A mean can be pulled upward or downward by a few unusually large or small orders. Check the underlying records as well, particularly when a small number of transactions could make the average unrepresentative. Microsoft identifies AVERAGE among common formulas in its Excel functions reference.
Build formulas around your own records
Use consistent column headings and values so criteria match the data you intend to summarize. Before relying on a result, check that the formula ranges cover the records you mean to include and that labels such as “Unpaid” are entered consistently. These functions help organize and analyze spreadsheet data; they do not replace accounting or tax advice.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
