Google Spreadsheet: How To Sum on Conditional Tips, Tricks and Tutorials 21 AUG 2013

google docs iconI’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:

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


Related Posts:

About Craig Lotter

South African software architect and developer at Touchwork. Husband to a cupcake baker and father to two little girls. I don't have time for myself any more.