Anyone who works with large tables often knows that if you try to count specific cells manually, it’s almost impossible to avoid errors. But it doesn’t have to be that way. If you want to know how many cells contain numbers, Excel has a simple function that can help you. In Excel, COUNT immediately tells you how many cells in the selected range contain numbers. We’ll explain how it works.
When working with Excel on a daily basis, sometimes you need to know how many cells in a table contain numbers. For example, if you’re working with revenue results, you may need to check the number of entries. The function can also be used to calculate the sum of numbers from a wide variety of values. Excel’s COUNT function only counts numbers, dates, and numbers turned into text with quotation marks.
The Excel COUNT function only counts the number of cells, it does not total the values in the cells. There is another function in Excel that does that: SUMIF allows you to filter cell values by certain criteria (e.g. text criteria or special symbols like $) and then add them up.
All you have to enter for the COUNT function is the range to analyze. Entries are made in the form of cell references. Select a from-to range separated by a colon as the function input.
=COUNT(Value1;[Value2];…)The advantage of this function is that you can specify multiple ranges. If the cells you want Excel to check are not contiguous, you can simply specify multiple ranges and don’t need to restructure your entire table. You can integrate up to 255 additional ranges of different sizes into the COUNT function.
The COUNT function checks a range for entries that Excel treats as numbers:
=COUNT(B2:E2)
This formula gives you the number of cells containing numbers in a single row. You can also expand the search to an entire worksheet or include specific ranges if there are columns in the table you do not want to include.
=COUNT(B2:E5;G2:J5)You can combine COUNT with other functions. For example, you can calculate a mean from the number of cells counted in various ranges.
=AVERAGE(COUNT(B2:E5);COUNT(G2:J5);COUNT(L2:O5))
In this example, you are counting the cells with numbers in multiple ranges (independently) and then calculating the mean value.
Count only lets you count the cells with numbers. It does not include text that is not treated as numbers or Boolean values. This allows you to analyze numbers separately from other content. However, if you want to include all values, COUNTA can help. The function’s structure is identical to the COUNT function.
For example, COUNTA can be used to display the attendance rate at meetings in a given month.
=COUNTA(B2:E5;G2:J5)/32
In this example, we want to count all non-blank cells and divide them by the number of all available cells. Attendance is indicated with an “x” in the respective cell. If we now format the resulting cell as a percentage, we can determine the percentage attendance rate.
However, sometimes you want to count all the cells that are empty. You can do that using Excel’s COUNTBLANK function. This function also shares the same structure as the other two functions, but can only include one argument. As a result, you can’t include multiple ranges in one function, you have to aggregate the totals.
=COUNTBLANK(B2:E5)+COUNTBLANK(G2:J5)
If you add COUNTA and COUNTBLANK, the result is always the number of cells in the range analyzed. This can be useful, for example, if we go back to our formula for calculating the attendance rate.
=COUNTA(B2:E5;G2:J5)/(COUNTA(B2:E5;G2:J5)+COUNTBLANK(B2:E5)+COUNTBLANK(G2:J5))
In this formula, you no longer need to enter the total number of cells manually. Instead, you calculate it from the total of all blank and non-blank cells.
The COUNTIF function gives you even more control when selecting criteria. For example, you can use it to have Excel search for specific terms.
Excel’s if-then statement is one of its most helpful formulas. In many situations, you can create a logical comparison: if A is true, then B, otherwise C. To use this useful if-then formula in Excel, you first need to understand how it works and precisely how to use it. For example, which syntax rules does the IF function follow, and how can you extend the formula?
In Excel, we almost always work with cell references. Rather than entering values into formulas individually, we pull them from the cells of tables. The INDIRECT Excel function enables you to even obtain references from other cells. This can be helpful when working with multiple worksheets or when creating complex formulas, for example. We explain how to use INDIRECT in Excel.
Many people use dates regularly in Microsoft Excel. However, the problem with dates is that they don’t automatically distinguish between working days and weekends. Excel does, however, provide a function that allows you to analyze working days separately. Using Excel’s WORKDAY function, you can calculate deadlines more precisely.
When performing complex calculations in tables, you can quickly produce values that are not fit for everyday use because they have far too many decimal places. The ROUNDDOWN function in Excel can help with this. It uses a simple formula to simplify your workflow. You can use it to round down any number to the desired number of decimal places.
Provide powerful and reliable service to your clients with a web hosting package from IONOS.
View packages