Sometimes you may require to add when a given criteria is met. In such a case you use sum if statement. you could want to add if a the gender of the students are all females or are all males wereas the data contains both males and females or sum data for a give county only when the data contains data for different counties.

To use sumif function you must have a column where the criteria will be taken from and a colomun where the data to be added is placed in. The illlustration below will take you through the function in a very easy and simple way to remember and learn.

Sum if statement has syntax =sumif(range, criteria, [sum_range])

An example will make it more real.

Enter equals from the keyboard and type sumif, this formula can be picked from pop up menu that appears.

Once the formula is selected then you select the range for the criteria.

Then a comma (,) then you enter criteria

You enter criteria as a text eg “Kenya” or select the cell B16 which contains name Kenya.

Finally you type a comma and select the range where the figures to be summed are.

Then you press enter from keyboard.

Auto fill the formula for USA and Britain from one from Kenya.

Note the formula that we have has relative referencing.

However we want the range for criteria and for sum not to be relative.

So we lock that range using the dollar sign as shown below in the formula bar

In the figure above all cells are not locked now let’s lock the ranges using dollar sign

Note all the ranges are now locked. When we auto fill the formula these ranges will remain the same but the B16 where our criteria is will change accordingly.

So lets auto fill now

Now check the formula in the other cells and see the changes on B16 to B17 to B18. The rest remains intact