If you wanna follow the explanation of this post by looking at cell references, go to this post: Taking Microsoft Office Excel Web App for a spin
I’ll show you a practical use of the CountIf function that you can apply in both Microsoft Excel spreadsheets and Google Docs spreadsheets.
Let’s start with this sample spreadsheet:
I’m using the above spreadsheet to keep track of my progress during the course for my first driver’s license.
The CountIf function is being used in column Remaining classes for each discipline. For example, the actual formula in cell I17 is this:
I17 = H17 - countif(D3:I14, B17)
Let’s break this thing and explain each piece:
H17 is a fixed value = 18. I know it beforehand. In this case for the Transit Legislation discipline I must have a total of 18 classes.
The countif function has this form: COUNTIF(range, criteria). So the above countif(D3:I14, B17) is telling us that:
D3:I14 is the range of cells we’re searching and B17 is what we’re after… The range encompasses all the disciplines’ acronyms from 11/23/2010 to 12/9/2010 for all the timetable.
B17 is the discipline’s acronym, in this case it is TL.
Easy to understand, isn’t it?
Doing the above with countif I can tell how many classes I have scheduled for that discipline. Then I just subtract this value from the total necessary classes to have a picture of how many classes I still have to go through to fulfill the necessary ones.
Let’s do the math:
CountIf returns 18 because LT appears 18 times in the timetable. Making the substitution of values:
I17 = H17 - countif(D3:I14, B17)
I17 = 18 - 18 = 0
Now I know that if I follow the above schedule I’ll probably finish the theoretical course by 12/9/2010. Pretty good.
This was a simple and practical use of the countif function that came in handy in this situation.
