If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Add items in column 3 if column 1 and 2 conditions are met
This is what I am trying to do I am keeping track of my bank account using
excel. I have it set up to were I enter a category in one column and I have columns set up for credit and debit and amount. If the item is a credit I place an "x" in the credit column and if its a debit I place an "x" in the debit column. On the top of the work sheet I have a running total and to the right I have each debit broken down by category. Questions: 1. is there a way to make the running total identify that either the credit or debit column has an "x" in it, and if the debit column has an "x" subtract the value in the amount column? 2. when showing each expenditrue by category is there a way to only have it add together the values that have the debit column checked? (as it is now I have a sumif which checks the category column for a value ex. gas, food, etc. but I can't figure out how to have it also check the debit column) thanks for any help you all can provide |
#2
|
|||
|
|||
Assuming your running total is in column F (allowing columns for date,
category, debit, credit, and amount), and assuming that row 1 has column headings, F2 would have the formula =IF(C2="X",-E2,E2) F3 would use the formula =F2+IF(C3="X",-E3,E3) & just copy that formula down For the sums by category, use the formula =sumproduct(--($c$2:$c$1000="x"), --($b$2:$b$1000=cell_with_category_name) ,e$2:e$1000) "DB16" wrote: This is what I am trying to do I am keeping track of my bank account using excel. I have it set up to were I enter a category in one column and I have columns set up for credit and debit and amount. If the item is a credit I place an "x" in the credit column and if its a debit I place an "x" in the debit column. On the top of the work sheet I have a running total and to the right I have each debit broken down by category. Questions: 1. is there a way to make the running total identify that either the credit or debit column has an "x" in it, and if the debit column has an "x" subtract the value in the amount column? 2. when showing each expenditrue by category is there a way to only have it add together the values that have the debit column checked? (as it is now I have a sumif which checks the category column for a value ex. gas, food, etc. but I can't figure out how to have it also check the debit column) thanks for any help you all can provide |
#3
|
|||
|
|||
thats not really how I have it set up but thank you for the suggestion. I
figured out he first question I just used two sumif's. The "running" total I have is it the top of the and spans the width of all the columns. Kind of looks like this: running total: =sumif(credits column has x)-sumif(debits column has x) | Expenditure by Category| | description | category | credits | debits | amount | | category | =sumif() | | | | | | | | | | | | | | | | (Pie Chart of Expenses) Question 2 is the only one I can't figure out now 2. when showing each expenditrue by category is there a way to only have it add together the values that have the debit column checked? (as it is now I have a sumif which checks the category column for a value ex. gas, food, etc. but I can't figure out how to have it also check the debit column) thanks for any help you all can provide |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
2 Columns - Show extra items in Column B | orbojeff | General Discussion | 3 | October 7th, 2005 09:55 PM |
Running total w/2 columns - Excel | RagDyeR | Worksheet Functions | 2 | August 10th, 2005 04:28 PM |
Table Design | A. Williams | Database Design | 3 | April 29th, 2005 07:02 PM |
unable to repair inobox | Sudheer Mumbai | General Discussion | 1 | February 20th, 2005 11:55 AM |
Column E cell contents added into Column D contents (not overwriting data but mixing) | creativetechguy | General Discussion | 2 | August 5th, 2004 07:32 PM |