I’m putting together a spreadsheet with my bank transactions in Excel, and I wanted to figure out how much I’m spending in various different categories. So following the numbers, I created a number of columns, which I’m checking with an x depending on the category.
What I’d like it to do is at the bottom of each column of x’s, look at the column above it, look at each x, take the value in that row, and add them all together. How should I go about this?
Check out the SUMIF function.
I hope this won’t be too complicated. We’ll use the IF function. The IF function has three parts: A math statement which can be true or false, a value to use if it is true, a value to use if it is false. For example, “=IF(X=Y,5,72)” means that if X and Y are equal, use the value “5”, but if they’re not equal, use the value “72”.
Let’s say you’re using columns A through F for most of your info, column G has the dollar amount, and you have three categories in columns H I J, and that’s where you are putting the Xs.
Choose three columns off to the right side of the screen where you can’t see them. Let’s use P Q R. These columns will be like H I J, except that where H I J are either blank or X, P Q R will be either zero or the dollar amount.
The formula for P7 will be =IF(H7=“X”,G7,0)
The formula for Q7 will be =IF(I7=“X”,G7,0)
The formula for R7 will be =IF(J7=“X”,G7,0)
Columns P Q R will be full of numbers. But you never need to look at them, because all you care about is the totals, and they will appear at the top of columns H I J.
The formula for H2 will be =SUM(P:P)
The formula for I2 will be =SUM(Q:Q)
The formula for J2 will be =SUM(R:R)
This has SUMIF written all over it.
There is a more elegant way to achieve what you are trying to do, but you have to be a bit of an Excel geek. Insert a new worksheet and create a list of categories. Select that list and then give it a range name (like Categories). Then on your sheet with the data, just have one column where you list the categories, instead of a different column to check each one off. Create a data validation rule for the categories where it must be in a list and use
=Categories
as the range. Now each category can be selected from a dropdown box.
To see the total for a category, somewhere put another cell with the same data validation, and next to it use a SUMIF formula (read help on that).
This is just a sketch, not an exact recipe, and if you don’t have time to work this all out at least use SUMIF for in your existing spreadsheet.
I could prepare and provide an example of my solution if you’d like.
[QUOTE=CookingWithGas]
Create a data validation rule for the categories
[/QUOTE]
How do I do that? This may be the most useful way of doing it.
Also, would there be a way of making it so that if the row has an entry in column C, I have a choice of, say, expense categories, and if it has an entry in column D, I have a choice of revenue categories?
Finally, can you alter the colours in a spreadsheet automatically according to the data in it?
You will find data validation under Data, Validation 
I’ve not used it myself so check Help - there’s blow by blow instructions there. Should be no problem having two different sets of categories - just give the two lists two names.
For the different colours for different data you need conditional formatting - amazingly enough under Format, Conditional Formating.
[QUOTE=MarcusF]
You will find data validation under Data, Validation 
I’ve not used it myself so check Help - there’s blow by blow instructions there. Should be no problem having two different sets of categories - just give the two lists two names.
For the different colours for different data you need conditional formatting - amazingly enough under Format, Conditional Formating.
[/QUOTE]
Just a quick followup on this: Conditional Formatting is fun stuff, but keep in mind that Excel will only apply one format at a time, and it’s first-come-first-served.
Meaning, if Condition 1 and Condition 3 are both met by the value in that cell, Condition 1 will be the formatting shown. This can be useful at times, but if you don’t know to expect it, it can be a major “Huh?” moment.
Validation is easily in the top ten most complex subjects Excel has to offer, but it’s also ridiculously easy to use once you ‘get it’.
What it sounds like you want is the List option. The way this works is, you choose a cell that will become your drop-down list – say, A1. After selecting that cell, you go to Data->Validation… and choose the List option from the Allow box. Down below you’ll see a Source box appear. In that you’ll put the range that holds the values you want to choose from – your Categories, in this case. Click Ok.
Now the cell will only allow you to select from a list of choices, which correspond to your Categories. You can then use this cell in a SUMIF function to get the correct values.
(Quick summary of SUMIF: looks for a value in a range and sums the values in another range where a match is found. Using your example, it would look in a category column for X and add together all the values found in your amount column wherever a corresponding X appeared.)
This is getting a bit trickier, and I’m not sure I understand exactly what you’re after. Short answer, yes, but I’d need more detail.
IF I’m reading this right, you’ve got categories across the top (Expenses, Income, Luxury, etc), amounts down the side ($5, $35, $12, etc), and X’s in the columns under each category that the amount in the row applies to?
It seems to me you could save yourself a bit of hassle by simply making one Category column and putting the corresponding category in that column next to each transaction. Then a simple SUMIF could get you what you want.
Going further, you could make a list of every possible category, and next to that list put a SUMIF that shows you the totals for each category at a glance, and still only use one column to track categories.
Finally, using Data Validation, you could set up that Category column so that it only allowed you to choose values in the list.
Make sense? Or am I completely off-base here?
[QUOTE=Sofaspud]
This is getting a bit trickier, and I’m not sure I understand exactly what you’re after. Short answer, yes, but I’d need more detail.
[/QUOTE]
Basically what I want to do is this: The bank gives me a sheet in which the amount for each transaction is listed in column C if it is an expenditure, and column D if it is a deposit.
What I’d like to have happen is to have the drop-down menu that you’ve described, but I’d like the menu to contain options taken from one list (such as “rent,” “groceries,” “taxes”…) if there is an amount in column C, and taken from a different list (such as “job income,” “reimbursement,” “tax refund”…) if there is an amount in column D.
[QUOTE=matt_mcl]
Basically what I want to do is this: The bank gives me a sheet in which the amount for each transaction is listed in column C if it is an expenditure, and column D if it is a deposit.
What I’d like to have happen is to have the drop-down menu that you’ve described, but I’d like the menu to contain options taken from one list (such as “rent,” “groceries,” “taxes”…) if there is an amount in column C, and taken from a different list (such as “job income,” “reimbursement,” “tax refund”…) if there is an amount in column D.
[/QUOTE]
Ah! Enlightenment!
Okay. This is entirely possible, but – and here’s the rub – it can get VERY tricky to implement if you’re not comfortable in Excel.
The gist if it is, you need your drop-down menu to behave intelligently – to choose a different list to show based on some input somewhere – and it isn’t designed to do that. Excel’s Data Validation is built in such a way as to discourage that sort of behavior, actually. The good news is, you can get around it.
A different option would be to use two columns instead of one for your categories – one pulls from a list of expenditure-categories, the other from a list of deposit-categories. This is easy to implement and doesn’t require any funky formula action, but is not very streamlined and not exactly what you want.
I’m going to assume you want to use the one-column method. First things first: on your sheet somewhere (and it must be on the same sheet as your list – I can show you ways around this as well if needed, but for now go with it), create two lists, one of expenditure categories and one of deposit categories. Place them in different columns and make note of which is where – Ex. in G and Dep. in H, for example. Make sure they start at row 1.
Next, select your Category column (the entire thing). Open up the Data Validation bit, choose List, and put the cursor in the Source box. Here’s where it gets tricky: we’re going to use a function called INDIRECT(), which essentially lets you create a reference to a cell or range on-the-fly, and Excel will treat it just like you’d typed it in directly.
Here’s the formula, using the columns I’ve described above (change if necessary):
=INDIRECT(IF(C1<>0,“G1:G”&COUNTA(G:G),“H1:H”&COUNTA(H:H)))
So, in English, what this is doing is:
IF the value in C1 is different than 0, then use G1 to G-however many Expenditure categories I have as the range, otherwise, use H1 to H-however many Deposit categories I have as the range.
Note that Excel will automatically change C1 to C2, C3, C4 … and so on as needed for the entire column. Also note that adding Expenditures or Deposits is easy – just tack them on the end and they’ll start appearing in the list.
Final warning: make sure you type the formula EXACTLY as it appears. If you need to change it to fit your sheet, do so in Notepad or somewhere else first, then copy-paste it into the Source box in Excel, because otherwise you’ll have a heck of a time trying to fix things (arrow keys behave differently when typing in that box, as just one example of problems).
Make sense?