Formula - involving money
Posted by Maria on June 24, 2000 4:50 AM
I have set up an Excel spreadsheet (Version 97) with my incomings and outgoings. It is based on monthly calculations. I have a Paid column with Yes in if I have paid my bill. I want Excel to deduct this bill from my total incoming to show me what money I have left for the month. I have 16 bills a month and the formula would have to calculate if I had paid three or four bills. (eg take away the cost of all the bills paid from the total income). Any help is appreciated.
Posted by Maria on June 25, 0100 3:14 AM
I will try that now - thank you
Posted by Ivan Moala on June 24, 0100 3:18 PM
Maria
Have a look @ the online help for
Sumif.
From online Help via F1
Adds the cells specified by a given criteria.
Syntax
SUMIF(range,criteria,sum_range)
Range is the range of cells you want evaluated.
Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, "32", ">32", "apples".
Sum_range are the actual cells to sum. The cells in sum_range are summed only if their corresponding cells in range match the criteria. If sum_range is omitted, the cells in range are summed.
Example
Suppose A1:A4 contain the following property values for four homes: $100,000, $200,000, $300,000, $400,000, respectively. B1:B4 contain the following sales commissions on each of the corresponding property values: $7,000, $14,000, $21,000, $28,000.
SUMIF(A1:A4,">160000",B1:B4) equals $63,000
In your case the formula may look something like this;
=SUMIF(E6:E17,"=Yes",D6:D17)
where E6:E17 is your Yes or Paid column
and D6:D17 is the Outgoing column.
HTH
Ivan
