I’m busy scripting a quick and dirty spreadsheet to keep track of Chantelle’s various incomes and expenditures, and in the process adding a view as to the split between her business and personal finances.
Now the sum of the expenditure and income columns is obviously easy, just a simple =SUM function call, but the next little step is slightly more tricky: Given two columns, I only want to sum the values of the first column provided the value in the second column matches a certain condition.
As an example, consider the following data:
A | B | |
1 | Expense | Business |
2 | 150.00 | 1 |
3 | 100.00 | 0 |
4 | 125.00 | 1 |
If I want to get the total expenses that are marked as being business related, then I need to run a =SUM function on column A but only include values when the associated value in column B is 1. To achieve this we make use of the =SUMIF function which essentially allows us to sum values on condition. The syntax is as follows:
=SUMIF(Range, Criteria, Sum Range)
where Range is the group of cells the function is to search on (i.e. run the conditional criteria against), Criteria is the conditional that determines whether the cell is to be summed or not, and Sum Range which is the data range that is to be summed if the first range meets the specified criteria. Note, that if Sum Range is omitted, the first range is then summed instead.
So we can solve our example below by running =SUMIF(B2:B4,’=1′,A2:A4), which would give us a value of 275.00
Nifty.