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!
posted by ktr at
7:42 PM
1 Comments:
Hey Josh - glad you like the articles! As for the "SUMPRODUCT" function, that's DEFINITELY the "fastest" way to do it, but might not be the most flexible. For those who don't know, SUMPRODUCT simply multiplies each element in two ranges, and returns the "sum of their products". Therefore, if you use 1's and 0's, you can get the effect of doing a sumif along multiple criteria. (I assume this is how you did it Josh, but please correct me if I'm wrong). In another article, I may get into optimization which uses SUMPRODUCT extensively.
An alternative solution (that might be more flexible) is to use array formulas. Josh, I assume that when you want to sum across multiple criteria and then you want to look at it from a different angle (perhaps different customers or different dates, etc.) you have to go through your 1's and 0's and change them up to reflect your new criteria? If so, then array formulas can definitely help. They're sort of like black magic, so I may have to write more extensively, but essentially you could write a formula like the following:
"=SUM(IF( (A1:A10)="Pete's" * (B1:B10)>"12/31/2004" * (B1:B10)<"1/31/2005",C1:C10))"
This nasty little formula would say "if the customer name in column A is Pete's and the invoice date in column B is greater than 12/31/2004 and also less than 1/31/2005, then include the values in column C in my summation". You would HAVE to hit Ctrl-Shift-Enter when you finished the formulas so Excel would know to loop through each of the cells to calculate the array formula. The reason they're more flexible is because you can (instead of hardcoding the values like I did) simply put the criteria into other cells and then copy the formula; no need to change 1's or 0's to find out different information. Look up in Excel's help menu "Array formulas" or simply express your interest and I can write an article on the topic if you prefer.
PS - An Access database would be even MORE flexible and FASTER as well. If you dumped the data into an Access (or any SQL based product such as MySQL or PostgreSQL or SQLite ... all open source and therefore free alternatives ;), you could do a quick "GROUP BY", query that would allow you to sum, average, etc. by MUCH more criteria and it would be easier to maintain in the long run. That's the approach I would recommend taking if possible. I can write an article on this as well, if there's any interest.
Good luck and hope that helps!
By
ktr, at 7:20 AM
Post a Comment
<< Home