Count and Sum Function in Excel

Count and Sum Function in Excel

Count

The count function is MS Excel is very important. This function allows you to count the number of cells. The function has multiple variants that allow conditional counting. This tutorial will discuss these variants.

Count

The count function is the basic function used for counting cells having numerical values. It doesn’t count cells with non-numeric data.

Count function

The syntax for Count function is         =Count(“Cell Range”)

numeric values

In the above figure, the total count is 6 which means, the formula has only counted cells with numeric values.

CountA

The CountA function is the basic function used for counting cells having all values. It doesn’t count blank cells data.

CountA function

The syntax for CountA function is       =CountA(“Cell Range”)

total count

In the above figure, the total count is 9 which means, the formula has counted all cells with values. Please note that this function doesn’t count blank cells.

CountBlank

The CountBlank function is the basic function used for counting cells that are blank. It doesn’t count cells with values.

CountBlank function

The syntax for CountBlank function is =CountBlank(“Cell Range”)

count is 3

In the above figure, the total count is 3 which means, the formula has only counted cells that are blank.

Countif

Countif function is a conditional count function. It counts values in a given range on the basis of given criteria.

Countif function

The syntax for Countif function is =Countif(“Cell Range”, “Criteria”). In the above figure, Range is defined from A1 to A9, whereas Criteria is set to Red. So now the formula will look for the word Red in the given range and will count its occurrence within the given cell.

Red

In the above figure, the total count is 3 which means, the word Red has occurred 3 times in the given range.

CountIfs

Countifs function is a conditional count function. It counts values in given ranges on the basis of given criteria. This function support multiple criteria for multiple range and the AND operation is performed between these criterias.

Countifs function

The syntax for Countifs function is =Countifs(“Cell Range 1”, “Criteria 1”, “Cell Range 2”, “Criteria 2”, ….). In the above figure, Range 1 is defined from A1 to A9, whereas Criteria 1 is set to Red similarly, Range 2 is defined from B1 to B9, whereas Criteria 2 is set to 45. So now the formula will look for the word Red in Range 1 along with having 45 as value in the adjacent cell in Range 2.

count is 1

In the above figure, the total count is 1 which means, the word Red has occurred only 1 time when its adjacent cell in Column B had a value = 45.

SUM

The sum function is MS Excel is very important. This function allows you to sum the values of cells. The function has multiple variants that allow conditional sum. This tutorial will discuss these variants.

SUM

The SUM function is the basic function used for adding cells having numerical values.

SUM function

The syntax for SUM function is           =SUM(“Cell Range”)

total

SUMIF

SUMIF function is a conditional sum function. It add values in a given range on the basis of given criteria.

SUMIF function

The syntax for SUMIF function is =SUMIF(“Range”, “Criteria”, “Sum Range”). In the above figure, Range is defined from A1 to A9, whereas Criteria is set to Red and the Sum range is set to B1 to B9. So now the formula will look for the word Red in the given range and will sum the adjacent cells in Column B.

sum is 179

In the above figure, the total sum is 179 which means, the word Red has occurred 3 times in the given range and the sum of its adjacent cells is 179.

SUMIFS

SUMIFS function is a conditional SUM function. It adds values in given ranges on the basis of given criteria. This function support multiple criteria for multiple range and the AND operation is performed between these criteria’s.

sumifs function

The syntax for sumifs function is =Sumifs(“SUM Range”, “Range 1”, “Criteria 1”, “Cell Range 2”, “Criteria 2”, ….). In the above figure, Range 1 is defined from A1 to A9, whereas Criteria 1 is set to “Red similarly, Range 2 is defined from B1 to B9 and Criteria 2 is set as White and finally the SUM Range is set to C1 to C9. So now the formula will look for the word Red in Range 1 along with having White in Range 2 and will perform sum function on cells where both criteria have been met.

total sum is 99

In the above figure, the total sum is 99 which means, the word Red and word White has combine occurrence on2 points.

More Related Articles For You

    Microsoft Excel Tutorial

Search

Shortcodes Ultimate

 
Follow Us
 
BGP