This spreadsheet does a lot with three advanced functions.

Function | Goal |
---|---|

SUMIF | Find the total amount spent on a given day, by a given person, etc. |

IF | Show a total only if a location, etc., has been entered. |

VLOOKUP | Look 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.

Argument | Description | Example |
---|---|---|

criterion | statement or value that is either true or false | A1>59 |

value if yes | output when the criterion is true | "pass" |

value if no | output 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.

Argument | Description | Example |
---|---|---|

range | where to look for the criterion | B4:B400 |

criterion | what is being searched for | B2 |

summed values | corresponding range of values to add wherever criterion is found | I4:I400 |

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

Argument | Description | Example |
---|---|---|

search key | what is being searched for | C4 |

range | where to look for the search key and index | 'Tax Rates'!A2:B40 |

index | which column of the range contains the output values | 2 |

- Type =SUMIF(A4:A400,A2,I4:I400) in cell A3.
- Type a date in cell A2 to see how much was spent that day.
- Change the formula in B3 to =IF(ISBLANK(B2),"",SUMIF(B4:B400,B2,$I4:$I400)).
- Repeat this formula across row 3, up to column F.
- Type a location in cell C2 to see how much was spent at that location.
- Chage the formula in I4 to =IF(ISBLANK(C4),"",G4*H4*(1+VLOOKUP(C4,'Tax Rates'!A$2:B$40,2,0)))
- Repeat this formula all the way down the spreadsheet.

➜ Try it

Debugging

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.