Google Spreadsheets: formulas tutorial

After my previous post about how to implement a Project Plan and manage activities with Google Spreadsheets, I received some emails which asked to me to explain some useful formulas used frequently into spreadsheets. I published an example's spreadsheet which you can see here whith an example about these functions:

- Count If
- Vertical Look Up
- Sum If
- Count Blank

You can use these functions, with the same syntax, using Excel (remember only to use ";" to separate functions' attributes and not ",").


Download this tutorial for Excel

Take a look at Google Spreadsheet file

On-line presentation

If you want you can also take a look at this presentation:




Count If
countif() is a very useful function which you can use to count an element in an interval. In the following example I'm looking foor how many times is repeated the name "Lara" into the interval. The result is 2.

Formula is:

=countif(B13:B17,B8)

...we can "traslate" this formula in this way: count how many times the value contained in the cell B8 is repeated in the interval B13:B17.


Vertical Look Up
vlookup() is another useful function which I use frequently to manage data in a spreadsheet. You can use it to find a value (not repeated) in an interval and return a value contained ina column in the same row of the found value. In the following example I found City and Age for "Jason". The result is Dublin and 34:

Formula is:

=vlookup(F8,F13:G17,2,)

... search for the value in the cell F8 (Jason), in the interval F13:G17, and return the value contained into the interval column with index 2 (Column G).


Sum If
sumif() executes a conditional sum of values contained in an interval based on some criteria:

Formula is:

=sumif(J11:K15,J6,K11:K15)

...sum in the range J11:K11 the values contained in the column Points (interval K11:K15) where the value in the interval J11:J15 is equal to J6.


Count Blank
countblank() count how many blank (empty) cells there are in an interval:


Formula is:

=countblank(N11:N15)

Count blank cells in interval N11:N15.

I suggest you to download the spreadsheet in .xls format or take a look at the Google Spreadsheets file here.