Advanced Functions

This spreadsheet does a lot with three advanced functions.

SUMIFFind the total amount spent on a given day, by a given person, etc.
IFShow a total only if a location, etc., has been entered.
VLOOKUPLook up the tax rate by location so that the correct sales tax can be calculated for each total.

The IF(criterion, value if yes, value if no) function is used to make a cell one value or another depending on a given criterion.

criterionstatement or value that is either true or falseA1>59
value if yesoutput when the criterion is true"pass"
value if nooutput when the criterion is false"fail"

The SUMIF(range, criterion, summed values) function is used to add only certain values in the sheet, based on the given criterion.

rangewhere to look for the criterionB4:B400
criterionwhat is being searched forB2
summed valuescorresponding range of values to add wherever criterion is foundI4:I400

The VLOOKUP(search key, range, index, 0) function looks up a corresponding value in a later column.

search keywhat is being searched forC4
rangewhere to look for the search key and index'Tax Rates'!A2:B40
indexwhich column of the range contains the output values2
    ➜ Try it
  1. Type =SUMIF(A4:A400,A2,I4:I400) in cell A3.
  2. Type a date in cell A2 to see how much was spent that day.
  3. Change the formula in B3 to =IF(ISBLANK(B2),"",SUMIF(B4:B400,B2,$I4:$I400)).
  4. Repeat this formula across row 3, up to column F.
  5. Type a location in cell C2 to see how much was spent at that location.
  6. Chage the formula in I4 to =IF(ISBLANK(C4),"",G4*H4*(1+VLOOKUP(C4,'Tax Rates'!A$2:B$40,2,0)))
  7. Repeat this formula all the way down the spreadsheet.

A formula will not always work the first time you try it. Debugging is a normal, expected part of the process, just as proofreading is for writing.

  • Check for punctuation errors. In particular, check that all commas, parentheses, and quotation marks are in place.
  • If an error is stated, google the error.
  • See the documentation for the function you are using.
  • For complex formulas, try a simpler version. If that works, add on a little bit at a time to see what part causes the error.
  • Ask me or someone else to take a fresh look at the formula.