SheetLever

SUMIF adds up numbers that meet one condition. SUMIFS does the same for several conditions at once — and takes its arguments in a different order. =SUMIF(A2:A100, "Riyadh", C2:C100) =SUMIFS(C2:C100, A2:A100, "Riyadh", B2:B100, "Hardware") Look at where C2:C100 — the column being added — sits in each. In SUMIF it's last. In SUMIFS it's first. That single inconsistency causes more broken formulas than everything else about these two functions combined. The argument order Function Order SUMIF where to look, what to look for, what to add SUMIFS what to add , where to look, what to look for, (repeat) A useful habit: use SUMIFS even when you only have one condition. It works perfectly with a single criteria pair, and you stop having to remember which order you're in. =SUMIFS(C2:C100, A2:A100, "Riyadh") Same total, different order — SUMIF ends with the sum range, SUMIFS begins ...

The IF formula asks a question and returns one answer if it's true and another if it's false. The pattern never changes: =IF(B2>=50, "Pass", "Fail") Three parts, separated by commas: the test, the answer when it's true, the answer when it's false. Everything else people do with IF is a variation on those three slots. Below are seven examples taken from real spreadsheet work, roughly in order of how often you'll need them. The three parts Part In the example What it does Logical test B2>=50 A question Excel answers with TRUE or FALSE Value if true "Pass" What appears when the test is TRUE Value if false "Fail" What appears when the test is FALSE Text answers go in double quotes. Numbers don't. "50" in quotes is text and will not compare correctly against a number. 1. A simple threshold =IF(B2>=50, "Pass", "Fail") A score of exactly 50 ...

Excel's Remove Duplicates tool is under Data → Remove Duplicates. It works, and it deletes rows permanently with no undo once the file is saved. That's the part nobody mentions. Before you use it, there's one question worth answering: do you actually want the duplicates gone, or do you just want to see them? Those are different jobs, and reaching for the delete button first is how people lose data they needed. This guide covers four methods, from the safest to the fastest, and the five traps that make Excel delete the wrong rows. Decide first: find, or remove? What you want Method Destructive? See which rows repeat Conditional formatting or COUNTIF No A clean copy, original kept UNIQUE, or Advanced Filter No Delete duplicates in place Remove Duplicates Yes A repeatable cleanup Power Query No If you're at all unsure, work non-destructively. A second copy of the data costs nothing; a deleted row you needed costs an afternoon. ...

INDEX MATCH is a two-step lookup: MATCH finds which row your value is in, and INDEX returns whatever sits in that row of another column. Written out: =INDEX($B$2:$B$100, MATCH(D2, $A$2:$A$100, 0)) That reads as: find D2 somewhere in column A, note which position it's in, then give me the value at that same position in column B. It looks more complicated than VLOOKUP, and for about ten minutes it is. What you get in exchange is a lookup that searches in any direction and doesn't break when someone inserts a column — the two failures that eventually catch out every VLOOKUP user. Understanding the two halves The combination is easier to learn if you build it in two steps rather than reading it as one formula. MATCH returns a position, not a value =MATCH("PRD-102", $A$2:$A$100, 0) If PRD-102 is the second entry in that range, this returns 2 . Not the price, not the product name — just the number 2. The 0 at the end means exact match, and it is the same t...

If your Excel has XLOOKUP, use XLOOKUP. If it doesn't, use VLOOKUP. That's the honest answer, and for most people the decision ends there — it depends on your Excel version, not on your skill level or the kind of data you have. The complication is that plenty of people have XLOOKUP and shouldn't use it, because the files they build get opened by colleagues who don't. This guide covers both: what XLOOKUP actually does better, and the specific situations where reaching for it will break someone else's spreadsheet. Do you have XLOOKUP? Type =XLOOKUP( into any cell. If Excel autocompletes the function name, you have it. If you get #NAME? , you don't. Version XLOOKUP available? Microsoft 365 Yes Excel 2024 Yes Excel 2021 Yes Excel 2019 and earlier No Excel for the web Yes Google Sheets Yes (added 2024) Excel 2019 is still widely installed in companies that buy perpetual licences rather than subscriptions. That single row...

VLOOKUP searches for a value in the first column of a range and returns something from a column to its right. The formula looks like this: =VLOOKUP(D2, A2:B50, 2, FALSE) That reads as: take the value in D2, find it in column A, and give me the matching value from column B — exact matches only. That single formula covers most of what people need VLOOKUP for. The rest of this guide explains each part, walks through two real examples, and fixes the five errors that trip everyone up. What VLOOKUP actually does Think of a product list with codes in one column and prices in the next. You have a code and you want its price. VLOOKUP is the function that goes and fetches it, instead of you scrolling and copying by hand. The "V" stands for vertical, because it searches down a column. There is an HLOOKUP that searches across a row, but you will rarely need it — most spreadsheets are built with data running downward. One rule matters more than any other: the value you are ...