Sometimes, you may want to sum values from a column, but only include ones which contain a specific value (eg a word) in another column in the same line.
Or to add together values from a row, but only include ones which contain a specific value in another row in the same line.
For example, in this spreadsheet I want to show the overall total of course durations, and also a sub-total that only includes courses for 3rd-level students.
How to add values together
The sum formula, for calculating find the overall total, is very easy: it is just =sum(B2:B4)
How to conditionally add values together
Applying a condition is only a little more complicated: you need a formula that says "add these together, based on the corresponding value of that".In Google Sheets, the SumIf() formula is the one to use.
It takes up three values inside the brackets (ie parameters). They are:
Range: The set of cells to check for the specific value (ie the 'criterion') Criterion: The pattern or test to apply Sum-range: The range (ie set of cells) to be added up. Note: you only have to inlcude this if it is different from Range.
And the formula is used like this:
The example above shows adding the values based in a column, based on another column. But the technique works just as well for rows, using a row-wise range statement like this:
Extra for Experts
The condition does not have to just be a value. It can also be a range condition, like:SumIf(b:b, >0, e:e)
There is more information about the SumIf() function here: https://support.google.com/drive/answer/3093583?hl=en-GB
No comments:
Post a Comment