### More "Database" Functionality in Excel

Continually trying to expand on what I've already covered, I want to discuss some features of Excel that you can use in place of Pivot Tables or VLOOKUP's for simple "queries". In this post, I would like to discuss the "SUMIF" formula and the "COUNTIF" formula. These allow you to get subtotals for a given range without having to manually parse the data. In one of my recent posts, I discussed Pivot Tables. These are extremely powerful tools, and I suggest you learn to use them whenever possible. Even so, sometimes you may not want to go through the motions of creating a pivot table for a quick and dirty analysis. In these instances, you can use one of these two formulas.

The simpler of the two is COUNTIF. You simply type "=COUNTIF(" and then highlight the range you want to evaluate, then a comma followed by the evaluation criteria (typically in quotes). For example, to count the number of cells that equal the word January, you might type "=COUNTIF(A1:A10, "January")".

The SUMIF is slightly more complicated, but not much. You can keep it just as simple as COUNTIF by using the same methodology. For example, "=SUMIF(A1:A10, "<100")" will sum the values in cells A1 - A10 that are less than $100. But what if you wanted the sales totals for the month of January? In this case, you'll have to get a little more complicated by using 2 ranges: 1 for the criteria and 1 for the values. Following our original example, your formula would be: "=SUMIF(A1:A10, "January", B1:B10)" (assuming month names are in column A and the corresponding sales figures are in column B). Simple, eh?

You can get more complicated by combining the two. For example, to get the average sale in January, you would use SUMIF followed by COUNTIF, then divide the first result by the second. You get the idea. Good luck!

The simpler of the two is COUNTIF. You simply type "=COUNTIF(" and then highlight the range you want to evaluate, then a comma followed by the evaluation criteria (typically in quotes). For example, to count the number of cells that equal the word January, you might type "=COUNTIF(A1:A10, "January")".

The SUMIF is slightly more complicated, but not much. You can keep it just as simple as COUNTIF by using the same methodology. For example, "=SUMIF(A1:A10, "<100")" will sum the values in cells A1 - A10 that are less than $100. But what if you wanted the sales totals for the month of January? In this case, you'll have to get a little more complicated by using 2 ranges: 1 for the criteria and 1 for the values. Following our original example, your formula would be: "=SUMIF(A1:A10, "January", B1:B10)" (assuming month names are in column A and the corresponding sales figures are in column B). Simple, eh?

You can get more complicated by combining the two. For example, to get the average sale in January, you would use SUMIF followed by COUNTIF, then divide the first result by the second. You get the idea. Good luck!