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.

posted by Phire to Computers & Internet (4 answers total)

Best answer: SUMIF is your friend.
posted by Simon_ at 4:58 AM on June 21, 2011

Response by poster: Rocking! Thanks!
posted by Phire at 5:01 AM on June 21, 2011

Best answer: 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

Best answer: 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 [1 favorite]