Excel functions, explained in plain words

Every function note

• COUNTIF in Excel, count the rows that match

  COUNTIF counts the cells in one range that meet one condition. The criteria decides everything, and text criteria need quotation marks.

• IF in Excel, one test and two answers

  IF runs a single comparison and returns one value when it is true and another when it is false. The order of the two results is the part people get wrong.

• INDEX MATCH in Excel, either direction

  MATCH finds which row a value sits on, INDEX returns the value at a row and column. Nested, they look up a value and can look left, which VLOOKUP cannot do.

• SUM in Excel, the whole column in one number

  SUM adds the numbers in a range and ignores anything that is not a number. It is the first formula most people type and the one most often pointed at the wrong range.

• SUMIF in Excel, add only the rows that match

  SUMIF adds the values in one column where the matching column meets a condition. The third argument is the column to add, and leaving it out is the usual mistake.

• TRIM in Excel, remove the breaking spaces

  TRIM removes spaces from the ends of a cell and collapses doubled spaces inside it. Imported lists arrive full of them, and they are the usual reason two values that look identical do not match.

• VLOOKUP in Excel, exact match first

  VLOOKUP finds a value in the first column of a table and returns something from the same row. The fourth argument decides whether it has to match exactly.

• XLOOKUP in Excel, no column counting

  XLOOKUP takes the value to find, the range to search and the range to return. Two ranges instead of a table and a column number, and a fifth argument for the missing case.

The questions these notes answer

Each page starts from a question and gives one formula that answers it. It ends on the same exercise the note uses.

• Why is my count lower than the number of rows?

  A column of six readings, and COUNT answers 3. The words, the blank and the seven that arrived as text are all skipped. SUM still adds 35.7, so a short count is the fastest way to find bad cells.

• How do I count how many cells have anything in them, dates included?

  Three dates and two blanks, and COUNTA answers 3 because it counts anything filled. COUNT answers 0 on the same column, since these entries arrived as text.

• How do I count how many rows have a particular value?

  COUNTIF counts the rows that meet one condition. Four of these eight tickets read Open, so it returns 4, and the quoting of the condition is the part people get wrong.

• How do I stop a lookup showing #N/A when the value is not in the list?

  The fourth argument of XLOOKUP is the text to show when nothing matches, so the cell reads as a sentence. It has to be the fourth argument; anywhere else it is read as a column.

• How do I look a value up when the labels run across the top row instead of down a column?

  HLOOKUP searches the first row and reads down the row number you give it, so Feb against a month header row returns the price and not the units. The row number counts rows the way VLOOKUP counts columns.

• How do I look up a value that sits to the left of the key?

  MATCH returns the row a price sits on, INDEX reads the product name from that row, and the answer comes from a column VLOOKUP cannot reach.

• How do I always round up instead of to the nearest number?

  476 units at twelve to a crate is 39.67. ROUNDUP orders 40 crates because the part crate ships, ROUNDDOWN counts the 39 full ones, and ROUND agrees with ROUNDUP today by luck.

• How do I look up the latest entry when a key appears more than once?

  Search mode -1 walks the key column from the bottom, so a repeated SKU returns its newest price. The same formula with the default search mode returns the oldest price instead.

• How do I add up only the rows that match one value?

  SUMIF adds the rows whose condition matches, which is why the third argument exists. Three North rows add to 900 while the whole table holds 1450.

• How do I look up a number that is at the crossing of a row and a column?

  One MATCH finds the row label, the other finds the column label, and INDEX reads the cell where they meet. It works on a region-by-quarter grid with no helper column.

• How do I look up which band a number falls into?

  Approximate match walks down the first column and stops at the last band that does not overshoot. So 4200 lands in the band that starts at 1000, and the rate is 0.05. A sorted first column is the whole contract.

• How do I look up a value when I only know the start of the name?

  A7&"*" turns a partial name into a pattern, and VLOOKUP returns the first row that matches it. The wildcard lives in the lookup value, so the cell stays editable.

Every Excel problem page

Practise on the exercise track

Reading a formula does not make you remember it. Each note carries an exercise from the 86-exercise track, so you type the formula on a sheet that answers back.

• Start with the first exercise
• See the whole track