A little explanation on the SUMIF function in google spreadsheets. Understanding this function will help you understand many other spreadsheet functions.

Let’s say we’ve got a simple list of people and amounts:

Let’s say, we want to know how much each person has paid.

We insert some extra columns:

And the formula:

=SUMIF($A2:$A17, D2, $B2:$B17)

Dragg the cell across the 4 other columns to copy the formula.

You will now see the combines amounts of the 4 names.

SUMIF

Ok, but how dows it work?

SumIF(argument1, argument2, argument3) needs 2 arguments to work, but you can use it with 3 if you want.

Argument1: The ‘search’ argument. This is the range of cells you want to search in.

Argument2: Criteria, this is ‘what we are looking for’.

Argument3: the ‘add to’ argument.

If the functions finds argument2(criteria) in argument1, it will add argument3 to the total.

… maybe this image will clear things up:

It looks through the entire range of argument1, and everytime it finds argument2, it adds the same row from argument3.

$

The $ is an amazing help when putting together forumla’s.

The $ locks the cell that is attached in the formula.When you expand the formula by dragging, the cells with a $ infront stay the same.

In the above example, the following 4 formula’s where created just by dragging the first cell:

=SUMIF($A2:$A17, D2,$B2:$B17)

=SUMIF($A2:$A17, E2,$B2:$B17)

=SUMIF($A2:$A17, F2,$B2:$B17)

=SUMIF($A2:$A17, G2,$B2:$B17)

The A2:A17 was locked in place by the $.

If I tried the same thing without the $ :

=SUMIF(A2:A17, D2,B2:B17)

=SUMIF(B2:B17, E2,C2:C17)

=SUMIF(C2:C17, F2,D2:D17)

=SUMIF(D2:D17, G2,E2:E17)

Everything just changes about! Choas!