Excel problems, answered with one formula

Counting rows and cells

• 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.

Looking up a value

• 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 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 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.

Rounding to a usable number

• 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.

Adding up rows that match

• 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.

Practise on the exercise track

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

• Start with the first exercise
• Read the function notes