Friday again Excellers, and today we are going use the COUNTIF function and get Excel to automatically to check how many classes students have signed up for …or….simply ..count how many times a person’s name appears in a data range.
I have my weekly classes information. You can see my sheet below. The students need to sign up for one class in each module. Slots are limited and the students need to insert their name in the class slots. The classes are in a contiguous range of data.
So, as well as wanting to know how many classes each student has signed up for, I want the number of classes to also automatically change as their name is added or taken away from any class.
The syntax of the COUNTIF function is
=COUNTIF(range, criteria)
range- this is required in the formula and is the group of cells that you want to count. The range can contain numbers, arrays or references that contain numbers. Blank and text values are ignored.
criteria- this is also required and can be a number, expression, cell reference or text string that determines which cells will be counted.
Let’s start the formula with the = sign and COUNTIF, then select the range of cells that are our data range in this example it is C3:F8, and the criteria is the name we want to count in the data range.
As the names are added or taken away we can tell how many classes each student has signed up for. Easy and that.
- View all of your Excel formulas at once
- Hide all of your Excel formulas from prying eyes!
- Undertand and debug Excel formula errors
- How To Display a Plus + Sign On Positive Numbers With Custom Formatting In Excel
[amazonjs asin=”1118902688″ locale=”US” tmpl=”Small” title=”101 Ready-to-Use Excel Formulas (Mr. Spreadsheet’s Bookshelf)”]
[amazonjs asin=”1118490444″ locale=”US” tmpl=”Small” title=”Excel 2013 Formulas”]
[amazonjs asin=”0789748673″ locale=”US” tmpl=”Small” title=”Excel 2013 Formulas and Functions (MrExcel Library)”]