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

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.

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").