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
|
|||
|
|||
Use different formula to calculation based on conditions
Hi. It's the look of my Excel file: Item 1----Amount Category---Details: ... Item 2----Amount Category---Details: ... Item 3----Amount Category---Details: ... Item 4----Amount Category---Details: ... As you see: - A1 is the item's name; A2 is the price/amount/cost/value of the item - B1 is the category the item belongs to; B2 is the description of the item. Now I would like to do some math which are the following: 1) If the category type is income, add all of them 2) If the category type is one-off expense, add all of them & put a minus sign in front of the value 3) If the category type is daily expense, add all of them & times 30, & put a minus sign in front of the value 4) If the category type is Asset A, put each amount of the item in this calculation: (Amount-1000)*3/5 and so on How can I achieve this? Thanks a lot. -- Additional information: - I'm using Office XP - I'm using Windows XP |
#2
|
|||
|
|||
Use different formula to calculation based on conditions
"0-0 Wai Wai ^-^" wrote:
It's the look of my Excel file: Item 1----Amount Category---Details: ... Item 2----Amount Category---Details: ... [....] - A1 is the item's name; A2 is the price/amount/cost/value of the item - B1 is the category the item belongs to; B2 is the description of the item. What a screwy way to organize information in spreadsheet. Someone might have a solution for your arrangement. But it might be better to reorganize the spreadsheet in a more reasonable. Something like the following: Column A: Item Column B: Amount Column C: Category Column D: Description Row 1 for Item 1; row 2 for item 2; etc. That is probably not the answer you want to hear. But sometimes it is an important learn when you are using a tool fundamentally wrong or poorly. It will help you in the future, if not now. Then there are simple solutions to your problems, namely (assuming you have 10 items) .... 1) If the category type is income, add all of them =SUMIF(C1:C10,"Income",B1:B10) 2) If the category type is one-off expense, add all of them & put a minus sign in front of the value =-SUMIF(C1:C10,"One-off Expense",B1:B10) 3) If the category type is daily expense, add all of them & times 30, & put a minus sign in front of the value =-30*SUMIF(C1:C10,"Daily Expense",B1:B10) 4) If the category type is Asset A, put each amount of the item in this calculation: (Amount-1000)*3/5 =IF(C1="Asset A",(B1-1000)*3/5,"") I suspect you want something different for #4, but your description is not clear enough to know for sure. I suspect you meant to say that you want the sum of (AssetA Amount - 1000)*3/5. That would be: =(SUMIF(C1:C10,"Asset A",B1:B10) -1000*COUNTIF(C1:C10,"Asset A",B1:B10))*3/5 Hope that helps in some small way. It is not directly applicable to the organization of your data. |
#3
|
|||
|
|||
Use different formula to calculation based on conditions
Errata ....
I wrote: =(SUMIF(C1:C10,"Asset A",B1:B10) -1000*COUNTIF(C1:C10,"Asset A",B1:B10))*3/5 Of course, that should be COUNTIF(C1:C10,"Asset A"). |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formula based on Len | forever | General Discussion | 1 | November 3rd, 2005 12:44 PM |
SUM based on multiple conditions - SORRY, URGENT!!! | marika1981 | Worksheet Functions | 4 | February 18th, 2005 11:13 AM |
Counting an Array based on a calculation | HokieLawrence | General Discussion | 10 | February 16th, 2005 02:39 AM |
Array formula to count based on conditions | Pierre Archambault | Worksheet Functions | 2 | August 29th, 2004 05:29 AM |
Formula for average units sold based on # days in month sold | John Sebastian | Worksheet Functions | 1 | December 29th, 2003 07:46 PM |