Two of the most used Excel functions are IF (condition check) and SUMIF (sum by condition). For anyone working with reports, budgets or sales sheets, these are everyday tools. Below we explain both with examples based on Microsoft's official support pages.
What is the IF function?
According to Microsoft, the IF function lets you make logical comparisons between a value and what you expect. So an IF statement can have two results: one if the condition is true, another if it is false.
IF syntax
IF(logical_test, value_if_true, [value_if_false])
- logical_test (required) — the condition you want to test;
- value_if_true (required) — the value returned if the condition is TRUE;
- value_if_false (optional) — the value returned if the condition is FALSE.
IF examples
A few examples from Microsoft's page:
| Formula | What it does |
|---|---|
| =IF(C2="Yes",1,2) | Returns 1 if C2 is "Yes", otherwise 2 |
| =IF(C2=1,"Yes","No") | "Yes" if C2 equals 1, otherwise "No" |
| =IF(C2>B2,"Over Budget","Within Budget") | Shows "Over Budget" if spending exceeds the budget |
| =IF(C2>B2,C2-B2,0) | Calculates the amount over budget, or 0 if none |
If you need several checks, IF functions can also be nested inside each other.
Common problems with IF
- If a cell shows 0, nothing was entered for the value_if_true or value_if_false argument.
- The #NAME? error usually means the formula is misspelled.
What is the SUMIF function?
SUMIF adds up only the values in a range that meet the criteria you specify. For example, =SUMIF(B2:B25,">5") sums only the numbers greater than 5 in the range B2:B25.
SUMIF syntax
SUMIF(range, criteria, [sum_range])
- range (required) — the cells evaluated by the criteria. Blank and text values are ignored;
- criteria (required) — the condition that determines which cells are added: a number, expression, cell reference, text or function;
- sum_range (optional) — used when the cells to add differ from range. If omitted, Excel adds the cells in range.
SUMIF examples
Based on the examples on Microsoft's page:
- =SUMIF(A2:A7,"Fruits",C2:C7) — adds the amounts in column C for rows where the category in column A is "Fruits".
- =SUMIF(A2:A5,">160000",B2:B5) — adds the values in column B for rows where column A is greater than 160000.
- =SUMIF(A2:A5,">" & C2,B2:B5) — takes the condition from cell C2 instead of typing it into the formula.
- =SUMIF(B2:B7,"*es",C2:C7) — adds the amounts for products whose names end in "es".
Rules for writing criteria
- Text criteria and criteria with logical or mathematical symbols (like ">5") are enclosed in double quotation marks; a plain numeric criterion can be written without quotes.
- A ? matches any single character and \* matches any sequence of characters. To find an actual ? or \*, put a ~ in front of it.
When to use IF and SUMIF?
- When each row needs its own decision ("paid / unpaid", "within budget / over budget") — IF.
- When you need the total of only the rows that meet a condition (sales of one category, transactions above a certain amount) — SUMIF.
Summary
- IF tests a condition and returns one of two results: IF(logical_test, value_if_true, [value_if_false]).
- SUMIF adds only the values that meet a condition: SUMIF(range, criteria, [sum_range]).
- Text and symbol-based criteria go in quotation marks; ? and \* are wildcards.
- SUMIF gives incorrect results for strings longer than 255 characters.
Want to learn Excel through practical tasks? Write to Estavor Akademiya: +994 77 754 10 12
📚 Sources
🌐 The original article is in Azerbaijani; the official sources are available in Azerbaijani.
More articles
VLOOKUP vs XLOOKUP: what's the difference and which to use?
What is the difference between VLOOKUP and XLOOKUP in Excel? The key rule, exact match, Excel versions and examples — based on Microsoft Support.
How to create a PivotTable in Excel: step-by-step guide
How do you create a PivotTable in Excel? Preparing data, Rows and Values areas, Sum/Count, showing percentages and Refresh — based on Microsoft's guide.
How do you sign up for an online course?
How do you sign up for an online course? Estavor Akademiya's courses, who they are for, and 4 steps to start live online lessons.