OmniDocExplanation of Excel function examples: application scenarios of commonly used formulas
Using real scenarios such as sales details and price lists, it explains how to use SUMIFS, XLOOKUP, IF and IFERROR, how to check the results, where to check when errors occur, and help you apply the usage and examples of excel functions to your own tables.
Key points of this article
- Use SUMIFS to summarize based on multiple conditions, which is less likely to miss rows than filtering first and then manually summing.
- Prioritize XLOOKUP for finding matches. The old version uses INDEX plus MATCH instead. Both of them must first clean up the extra spaces in the number.
- Mixing text numbers into the amount column will cause statistical omissions. Use ISNUMBER to check first and then correct it.
- The reference area must be fixed when the formula is filled down, otherwise the range will be misaligned, and the result may appear normal but is actually wrong.
- When checking, manually calculate a few lines and compare the totals, which is more reliable than just looking at whether the formula reports an error.
Many people start learning Excel functions from the syntax description: how many parameters are there and what each parameter means. After memorizing it, I still don’t know where to start when faced with the real form. This article is intended for people who already have a sales record, list or ledger and need to calculate the results. It uses several specific scenarios to explain how to use excel functions and examples, and puts each function into a real table to see how it works.
What you can do after reading this is: determine which type of function should be used for a problem, write the formula accordingly, and know where to look when the result is wrong. The examples in this article can be reproduced using your own data, just replace the table names, column names and numbers with your own.
If you haven't come into contact with the basic syntax of functions, you can spend ten minutes to familiarize yourself with the writing method of "= function name (parameter)" before returning here.
Which function is suitable for your table? One sentence conclusion
One sentence conclusion: Use SUMIFS to summarize based on conditions, use search functions to obtain values based on numbers, use IF to group or mark, and wrap a layer of IFERROR around the formula when there may be errors.
| your situation | Recommended practices | reason |
|---|---|---|
| Statistics of amount or quantity by region, month, etc. | SUMIFS, COUNTIFS | Summarize by multiple conditions at once, no need to filter manually |
| Get the name or unit price from the price list by product number | XLOOKUP; when the version is older, use INDEX plus MATCH | Precise positioning based on keywords can still be used after the data is increased |
| Grade, mark status by amount or points | IF, if there are many gears, nest or use IFS instead. | The judgment criteria are clear and the results are automatically updated with the data. |
| Null values or errors may be encountered during calculations | Wrap a layer with IFERROR | Prevent errors from spreading to the summary and facilitate location |
Scenario: A sales statement, three things to do
You have a monthly sales report, about 300 lines, listing the date, salesperson, region, product number, quantity and amount; there is also a product price list, recording the number and standard unit price. There are three results that the leader wants: the total sales of each region, adding the product name and unit price in each line, and dividing the performance into three levels: A, B, and C according to the amount.
The stuck point is that manual filtering and then summing can easily miss rows; as soon as the data is updated, you have to do it all over again. The following process uses functions to fix these three things.
Complete process
- First organize the source data. The table header only occupies one row, the data type of each column is unified, and the merged cells are split first. If the data comes from a PDF report, you can first use the PDF editor of the OmniDoc desktop client to export it to Excel, and then process it according to the following steps.
- Do regional summary. List the region names in the summary area, enter =SUMIFS(amount area, region area, A2) next to it, and then fill down. The amount area should be fixed, that is, add a dollar sign in front of the column label and row number in the locked form, such as F2:F301 to the locked form.
- Add date conditions. When counting only a certain month, add a set of conditions to SUMIFS, such as date range,">="&start date, date range,"<="&end date.
- Complete the product name and unit price. Enter =XLOOKUP(D2, price list! A:A, price list! C: C, "Not Found") in the blank column of the detail table. XLOOKUP requires a newer version. If the version is not supported, use INDEX and MATCH instead.
- Make three levels of classification. The amount is in column F, enter =IF(F2>=10000,"A",IF(F2>=5000,"B","C")) in G2, and change the threshold to your own standard.
- Add fault tolerance to calculated columns. For possible error calculations, wrap a layer of IFERROR, such as =IFERROR (original formula, ""), to prevent error values from spreading into the summary.
- Sampling verification. Use filter to randomly select 5 rows, calculate them manually, then add the regional totals and compare them with the detailed totals.
- Translate when needed for English readers. You can upload the .xlsx file at omnidoc.top, select bilingual comparison or translation only, and download the results. After translation, table headers, numbers, and formula results must be checked item by item. Machine translation will inevitably lead to errors.
Where things can easily go wrong
- Text number. The amount looks like a number, but part of it is missing when summing. You can enter =ISNUMBER(F2) in a blank cell, and returning FALSE means it is text. The processing method is to convert into columns, or use special paste to perform a multiplication operation of 1 to convert it into a number.
- Misplaced reference. After dragging the formula down, the results suddenly look wrong. The way to find out is to look at the formulas of adjacent cells in the same column to see if the area is moving downwards. The solution is to select the area address and press the F4 key, add a lock symbol to the row number and column label, and fix the range.
- New row added to price list. If the price list is written in a fixed area such as A2:A50, the newly added number will not be included. The processing method is to reference the entire column, or convert the area into a table, so that the range automatically expands with the data.
- Meaning of error value. #N/A means not found, #VALUE! means type mismatch, #DIV/0! means divisor is zero. When encountering an error, first trace which reference it comes from layer by layer. Do not directly use IFERROR to cover all errors, otherwise the real problem will be covered up.
🎯 Which method is suitable for you?
If your data is a detailed account, it must be summarized according to multiple conditions.
Use SUMIFS or COUNTIFS first to add conditions one by one. This is less likely to miss rows than filtering first and then summing, and is easier to reuse later.
If you want to match two tables
It is preferred to use XLOOKUP to search accurately by number; when the version is older, use INDEX plus MATCH to achieve the same effect. Before searching, clean up the extra spaces and full-width differences in the number. Otherwise, a common situation is that the number obviously exists but cannot be found.
If your form is to be shown to others, the result cannot be wrong.
Each summary formula is subject to manual sampling verification, and a total verification cell is reserved next to it. IFERROR is added to the outer layer of the calculated column, but the original formula is retained to facilitate traceability by others.
If you need to share the results with English readers
First finalize the table header and formula results in Excel, and then upload the file to omnidoc.top for translation. After translation, numbers, dates, and formula references must still be checked to confirm that they are correct before sending them out.
❓ Frequently Asked Questions (FAQ)
1. There are many Excel functions, do you need to learn them all?
unnecessary. In daily work, functions such as summation, conditional summary, search, and judgment are basically enough. First learn the few mentioned in this article thoroughly, and then check the corresponding functions when you encounter new problems.
2. What is the difference between SUMIF and SUMIFS?
SUMIF can only set one condition, and SUMIFS can set multiple conditions. When statistics need to be calculated by region and month at the same time, it is more straightforward to use SUMIFS and less likely to make mistakes.
3. What should I do if the old version of Excel does not have XLOOKUP?
Use the combination of INDEX plus MATCH instead. First use MATCH to find the row position where the number is located, and then use INDEX to get the corresponding name or unit price. The results of the two are consistent, but the writing method is slightly longer.
4. The formula result is obviously correct, but why does the total still not add up?
There are three common reasons: there are text numbers in the amount, the summary area does not cover all data rows, or misaligned references cause individual rows to be miscalculated. By checking item by item according to the previous sampling check method, the cause can usually be found.
📝 Summary
The key to learning Excel functions is to first think clearly about which category the problem at hand belongs to, and then choose the corresponding function. Use SUMIFS to summarize by conditions, use XLOOKUP or INDEX plus MATCH to search by number, use IF for binning, and hand over fault tolerance to IFERROR. Each formula must be checked with several lines of manual data to make the results reliable.
