Greetings all,
I am an excel newbie, so please bear with me. I'm using Excel 2003 on Windows 2000 and Windows XP.
I'm creating a spreadsheet to help track personal spending, budget, etc. I want to create a formula that will calculate the net amount of credit card transactions. The spreadsheet contains a "register" where you enter the transactions, including date, description, and amount. There is also a pulldown menu to choose what type of transaction it is. This menu includes the names of the user's credit cards which are entered by the user on a separate sheet, plus "Savings" and "Flex" which is a slush fund. It looks like this:
Notice the $0.00 in "Net personal credit transactions." What I want to do is sum only those amounts where the column immediately to the left of it contains the name of one of the credit cards. In other words, don't sum it if the column to the left contains 0, Flex, or Savings. All other possible values will be credit cards.
The current formula is: {=SUM(IF(OR(I10:I29="Flex",I10:I29="Savings",I10:I29=0),0,J10:J29))}
So, my (apparently mistaken) understanding is that the OR function will return TRUE if any of those conditions are met, i.e., that the I column value is 0, Flex, or Savings. That should kick the TRUE out to the IF function, which ought to return 0. Otherwise, it will return the value in the J column. Whether IF kicks out 0 or the J column value, these should be summed by SUM. The result should be only the values that correspond to a credit card transaction. Shouldn't it?
Anyway, it appears that if it ever encounters a I column cell that contains anything except a credit card name, the result is 0. If all the I column cells contain a credit card name, the calculation will work. I don't want to have to redo the budget sheet just to make this work. It seems like it ought to work as it is, but I must be missing something.
Perhaps my trouble is that I misunderstand how arrays work. My understanding is that:
(1) Take I4, see if any of the conditions are true, return either 0 or J4, and SUM stores that (0 or J4) in some variable
(2) Go to I5, see if any of the conditions are true, return either 0 or J5, and SUM adds that (0 or J5) to whatever is stored in it's variable
(3) Wash, rinse, repeat.
Or, is it starting at I4, and check all cells to I25, and if any of those values is not a credit card, returns 0, then starts at I5, and checks to I25, etc., so that SUM gets a whole bunch of 0's?
If in fact it is the latter, is there anyway to make it do the former? I'd prefer not to use macros if possible. Sorry for the long post, I hope this is clear and adequately explains the difficulty I'm having.
I am an excel newbie, so please bear with me. I'm using Excel 2003 on Windows 2000 and Windows XP.
I'm creating a spreadsheet to help track personal spending, budget, etc. I want to create a formula that will calculate the net amount of credit card transactions. The spreadsheet contains a "register" where you enter the transactions, including date, description, and amount. There is also a pulldown menu to choose what type of transaction it is. This menu includes the names of the user's credit cards which are entered by the user on a separate sheet, plus "Savings" and "Flex" which is a slush fund. It looks like this:
Notice the $0.00 in "Net personal credit transactions." What I want to do is sum only those amounts where the column immediately to the left of it contains the name of one of the credit cards. In other words, don't sum it if the column to the left contains 0, Flex, or Savings. All other possible values will be credit cards.
The current formula is: {=SUM(IF(OR(I10:I29="Flex",I10:I29="Savings",I10:I29=0),0,J10:J29))}
So, my (apparently mistaken) understanding is that the OR function will return TRUE if any of those conditions are met, i.e., that the I column value is 0, Flex, or Savings. That should kick the TRUE out to the IF function, which ought to return 0. Otherwise, it will return the value in the J column. Whether IF kicks out 0 or the J column value, these should be summed by SUM. The result should be only the values that correspond to a credit card transaction. Shouldn't it?
Anyway, it appears that if it ever encounters a I column cell that contains anything except a credit card name, the result is 0. If all the I column cells contain a credit card name, the calculation will work. I don't want to have to redo the budget sheet just to make this work. It seems like it ought to work as it is, but I must be missing something.
Perhaps my trouble is that I misunderstand how arrays work. My understanding is that:
(1) Take I4, see if any of the conditions are true, return either 0 or J4, and SUM stores that (0 or J4) in some variable
(2) Go to I5, see if any of the conditions are true, return either 0 or J5, and SUM adds that (0 or J5) to whatever is stored in it's variable
(3) Wash, rinse, repeat.
Or, is it starting at I4, and check all cells to I25, and if any of those values is not a credit card, returns 0, then starts at I5, and checks to I25, etc., so that SUM gets a whole bunch of 0's?
If in fact it is the latter, is there anyway to make it do the former? I'd prefer not to use macros if possible. Sorry for the long post, I hope this is clear and adequately explains the difficulty I'm having.
