Basic Excel functions to help count and calculate sums for office workers

Basic functions in Excel such as Excel calculation and statistical functions,... are very useful for those who often have to work on Excel spreadsheets, especially in the fields of accounting and human resources administration.

Using Excel application, we will have to work with basic functions regularly. They will help to calculate data directly on the spreadsheet faster and more conveniently.

Excel provides a large number of functions to analyze, audit and calculate data by taking full advantage of the functions provided in Excel. They are much more convenient than expressions.

COUNT function

The COUNT function in Excel returns the number of numeric values. Numeric values include negative numbers, percentages, dates, times, fractions, and formulas that return numbers. Empty cells and text values are ignored.

Syntax: COUNT (value1, [value2], ...)

The COUNT function syntax has the following arguments:

value1: (Required) The first item, cell reference, or range for which you want to count numbers.

value2, ...: (Optional) Up to 255 additional items, cell references, or ranges for which you want to count numbers.

Note, arguments can contain or refer to many different data types, but only the index is counted.

If you want to count logical values, text, or error values, use the COUNTA function (COUNT counts the number of cells with numeric values, while COUNTA finds the number of cells with non-blank data).

Basic Excel functions to help count and calculate sums for office workers - 1 — If you want to count only numbers that meet certain criteria, use the COUNTIF or COUNTIFS function.

For example, if you need to count from cells B1 to B10, type =COUNT(B10:B10).

To sum cells based on multiple conditions (for example, blue and green), use the following SUMIFS function (the first argument is the spreadsheet range to sum).

To sum cells based on a criterion (for example, green), use the SUMIF function with three arguments (the last argument is the range to sum).

COUNTBLANK function to count empty cells

The COUNTBLANK function is classified under Excel's statistical functions. You use this function when you need to count blank cells.

In financial analysis, this function can be useful in highlighting or counting blank cells within a given range.

The syntax of the function is:

=COUNTBLANK(spreadsheet range to count)

In there:

The worksheet range to count specifies the range of cells in which you want to count blank cells.

As a worksheet function, COUNTBLANK can be entered as part of a formula in a cell of a worksheet.

If you want, you can use conditional formatting to highlight rows with blank cells using the COUNTBLANK function. Select the desired range and on the selected conditional formatting and apply the COUNTBLANK() function. This will then highlight all blank cells on the desired range.

COUNTA function to count non-empty cells

The COUNTA function is used to count the number of cells containing any content including numbers, letters or symbols, or in other words, it is used to count non-empty cells.

Syntax:

=COUNTA(spreadsheet range to count).

The COUNTA function counts cells that contain values, including numbers, text, logicals, errors, and empty text (""). COUNTA does not count empty cells.

The COUNTA function returns the number of values in a list of supplied arguments.

COUNTA takes multiple arguments in the form value1, value2, value3, etc. The arguments can be individual hardcoded values, cell references, or ranges up to a total of 255 arguments.

All values are counted, including text, numbers, percentages, errors, dates, times, fractions, and formulas that return empty strings ("") or empty cells are ignored.

SUM function

The SUM function in Excel adds numeric values in a range of cells, categorized as mathematical and trigonometric functions.

The function is entered by typing “=SUM” followed by the values to be summed. The values supplied to the function can be numbers, cell references, or ranges.

The SUM formula automatically updates when a user inserts or deletes a value. It also includes changes made to an existing range of cells. Furthermore, the function also ignores blank cells and text values.

For example, you want to add the numbers in cells A2 and B2 together, and then display the result in cell B3.

To do this, simply move to cell B3 and type the phrase "=SUM" and select the =SUM function that just appeared in the pop-up list.

Next, press the Ctrl key while clicking on cells A2 and B2 and finally press the Enter key.

The sum of the two numbers in cells A2 and B2 that you just selected will appear immediately in cell B3.

Basic Excel functions to help count and calculate sums for office workers - 2 — You can use the SUM function to calculate the sum of two or more cells simply by selecting the necessary cells in the function's content.

Conditional SUMIF function

The SUMIF function will sum cells that meet the given criteria. The criteria can be based on dates, numbers, and text. It supports logical operators like (>, , =) and also wildcard characters (*, ?).

The general formula for the SUMIF function in Excel is:

=SUMIF(range, criteria, [sum_range])

Arguments:

range: (required argument) This is the range of cells to which you want to apply the criteria.

criteria: (required argument) This is the criteria used to determine which cells should be added.

The criteria argument can be: A numeric value (which can be an integer, decimal, date, time, or logical value) a text string, an expression

sum_range: (optional argument) This is an array of numeric values (or cells containing numeric values) that will be added together if the corresponding range entry meets the provided criteria.

If the [sum_range] argument is omitted, values from the range argument will be summed instead.

For example, to sum cells based on a condition (for example, greater than 9), use the following SUMIF function (two arguments).

AVERAGE function

The =AVERAGE function does exactly what its name suggests, which is to return the average of the selected numbers. To calculate the average, Excel adds all the numeric values and divides them by the number of numeric values.

The AVERAGE function can handle up to 255 individual arguments, which can include numbers, cell references, ranges, arrays, and constants.

The syntax of the AVERAGE function is:

= AVERAGE (number1, [number2],...)

In there:

number1 : A number or cell reference that refers to numeric values.

number2: (Optional) A number or cell reference that refers to numeric values.

The AVERAGE function ignores logical values and numbers entered as text. If you need to include these values in the average, see the AVERAGEA function.

If the values supplied to AVERAGE contain errors, AVERAGE returns an error. You can use the AGGREGATE function to ignore errors.

For example, if you want to calculate the average value from cell A10 to cell J10, just type =AVERAGE(A10:J10) and press Enter. The result in cell K10 is the average value between cells A10 to J10.

Basic Excel functions to help count and calculate sums for office workers - 3 — The AVERAGE function calculates the average of the numbers provided as arguments.

Alternatively, you can use the mouse pointer to drag and drop and select the data area or you can simultaneously press the Ctrl key and then click on each cell separately in case the cells are not adjacent to each other.

Note, however, that the zero (0) value in C5 is included in the average, since it is a valid numeric value. To exclude zero values, use AVERAGEIF or AVERAGEIFS instead.

MIN, MAX function

To find the lowest value (smallest number) in a set of values, use the MIN function.

The MIN function has the following arguments in its syntax:

number1: required

number2 to number255: optional

For each argument, you can enter a number, name, array, or worksheet reference where the number is stored.

If the argument is a reference or array, any empty cells, logical values (true or false), or text values are ignored when calculating the minimum value.

If the range does not contain any numeric values, the MIN formula returns zero.

If you want to find the smallest number in a range of data, the =MIN function can do the job. Just type in the phrase =MIN(D3:J13) , and Excel will return the smallest number in that range.

In contrast to the =MIN function just mentioned, the =MAX function will return the number with the largest value in the range to be searched.

The syntax of this function is similar to the =MIN function, including the first cell address to the last cell. The Excel MAX function has the following arguments in its syntax:

number1: required

number2 to number255: optional

For each argument, you can enter a number, name, array, or worksheet reference where the number is stored.

If the argument is a reference or array, any empty cells, logical values, or text are ignored when calculating the maximum value.

If the range does not contain any numeric values, the result of the MAX formula is 0.

In Excel 2019 or Excel for Office 365, you can use the MINIFS and MAXIFS functions to find the minimum or maximum value based on one or more criteria. The MINIFS and MAXIFS functions are not available in earlier versions of Excel.

Vu Huyen (synthesis)

Useful
Emotion
Creative
Unique

Source