Does excel have a WHERE clause?
June 21, 2011 4:57 AM
I have an excel file with a list of expenses that are continually updated, sorted by date. There is a column for specifying the category that each expense belongs to. Is it possible to create a formula that adds up the expenses that belong to a specific category?
I know I can sort-by-category and then do it manually, but since it's important that this list be kept in order by date, it would be highly annoying to sort and resort every time I want to know what the running total for a specific category is.
I'm hoping I can do something like SUM(all items in COST where its corresponding entry in CATEGORY is X). Is this possible in Excel, or do I need to move my data to a database system? It's not a whole lot of data, just a couple of spreadsheets.
I would prefer not to keep a separate set of columns for each category.
I know I can sort-by-category and then do it manually, but since it's important that this list be kept in order by date, it would be highly annoying to sort and resort every time I want to know what the running total for a specific category is.
I'm hoping I can do something like SUM(all items in COST where its corresponding entry in CATEGORY is X). Is this possible in Excel, or do I need to move my data to a database system? It's not a whole lot of data, just a couple of spreadsheets.
I would prefer not to keep a separate set of columns for each category.
In addition to SUMIF linked above, Excel has a small set of functions that mimic true database functionality. Consider DSUM (and its cousins DCOUNT, DAVERAGE, DMIN, DMAX, and DGET).
posted by Nomyte at 5:49 AM on June 21, 2011
posted by Nomyte at 5:49 AM on June 21, 2011
You can also make a pivot table on this and just refresh regularly. This is useful if you want to see, say, expenses by category by month.
posted by jeather at 6:25 AM on June 21, 2011
posted by jeather at 6:25 AM on June 21, 2011
This thread is closed to new comments.
posted by Simon_ at 4:58 AM on June 21, 2011