You probably already know that Excel can do calculations with numbers. But did you know that it can also perform calculations on other types of data? One of the simplest examples is the function COUNTA (SCHYOTZ). Function COUNT looks at a range of cells and reports how many of them contain data. In other words, it looks for non-empty cells. This feature is useful in a variety of situations.
If you have never worked with Excel functions, then it will be useful for you to go through a series of lessons from the section Formulas and Functions our Excel Tutorial for Beginners. Function COUNT works the same in all versions of Excel, as well as other spreadsheets such as Google Sheets.
Consider the example
In this example, we are using Excel to plan an event. We sent out invitations to everyone, and when we get answers, we enter “Yes” or “No” in the column C. As you can see, in the column C there are empty cells, because the answers have not yet been received from all the invitees.
Counting responses
We will use the function COUNTto count how many people responded. In a cell F2 enter an equal sign followed by the name of the function COUNTA (SCHÖTZ):
=COUNTA
=СЧЁТЗ
As with any other function, arguments must be enclosed in parentheses. In this case, we only need one argument: the range of cells we want to check using the function COUNT. Answers “Yes” or “No” are in the cells C2: C86, but we’ll include a few extra lines in the range in case we need to invite more people:
=COUNTA(C2:C100)
=СЧЁТЗ(C2:C100)
After clicking Enter You will see that 55 responses have been received. Now for the fun part: we can keep adding results to the spreadsheet as we get responses, and the function will automatically recalculate the result to give us the correct answer. Try typing “Yes” or “No” in any empty cell in the column C and see that the value in the cell F2 has changed.
Counting the invitees
We can also count the total number of people we have invited. In a cell F3 enter this formula and press Enter:
=COUNTA(A2:A100)
=СЧЁТЗ(A2:A100)
See how easy it is? We just need to specify another range (A2:A100) and the function will count the number of names in the column First Name, returning the result 85. If you add new names at the bottom of the table, Excel will automatically recalculate this value. However, if you enter something below line 100, then you will need to correct the range specified in the function so that all new lines are included in it.
Bonus question!
Now we have the number of responses in the cell F2 and the total number of invitees in the cell F3. It would be great to calculate what percentage of invited people responded. Check yourself if you can write in the cell yourself F4 a formula for calculating the share of those who responded to the total number of invitees as a percentage.
Use cell references. We need a formula that will always be recalculated when changes are made to the table.