SUMIF Function in Excel

SUMIF is the function used to sum the values according to a single criterion. The SUMIF function is categorized under excel math & trigonometry function. Using this function, you can find the sum numbers applying a condition within a range. It will sum up cells that meet given criteria. The criteria are based on dates, numbers, and text. This function is used to find the sum of particular numbers within a large data set.

 

Syntax:

=SUMIF(range,criteria,[sum_range])

The formula uses the following arguments:

Range:
The range of cells that you want to apply the criteria against.

Criteria:
The criteria used to determine which cell to add.

Sum_range (Optional argument):
It indicates the range of cells to be added together.

How to use the SUMIF function in Excel?

SUMIF supports logical operators (>, <, <>, =) and wildcards (*,?) for particular matching. Let us understand the SUMIF excel function with the help of the following examples.

 

# Numeric number:
Let’s explore how to use SUMIF function based on numbers that meet specific criteria.

Example #1
In the following worksheet shown there are some numeric number. SUMIF is configured to sum values which is greater than 50. So, the formula will be =SUMIF(B2:B7,”>”&50). And the returns value will be 205.

Example #2
In the below example SUMIF is configured to sum values which is less than or equal to 20. So, the formula will be =SUMIF(B2:B7,”<=20"). And the returns value will be 46.

           

Example #3
The following worksheet shown that there are three arguments, last argument is the range of sum (B2:B7). SUMIF is configured to sum value which is contain 2020 in the range A2:A7. So formula will be =SUMIF(A2:A7,2020,B2:B7). And the returns value will be 23.4.

Example #4
A value from another cell can be included in criteria using concatenation. The SUMIF will return the sum of all sales over the value in E3. The formula in E4 is =SUMIF(B2:B7,”>”&E3) and the returns will be 395.

 

# Text strings:
Let’s explore how to use SUMIF function based on text strings that meet specific criteria. Notice that which is text, must be always enclosed in double quotation marks.

Example #1
In the following worksheet, SUMIF function is configured to sum value (range B2:B8) which is contained exactly East in the range A2:A8. The formula will be =SUMIF(A1:A8,”East”,B1:B8) and the returns will be 70.

 

Example #2
To express “not equal to criteria use “<>” operator by double quotes. In the below worksheet shown that if we need to sum which is not Apple from range of A1:A8 and value range is B1:B8 then the formula will be =SUM(A1:A8,”<>Apple”,B1:B8) and the returns will be 78.

 

Example #3
In the below format SUMIF function is configured to sum value in the range B1:B8 if the corresponding cells in the range A1:A8 contain exactly Banana or Cherry. Then the formula will be =SUMIF(A1:A8,”Banana”,B1:B8)+SUMIF(A1:A8,”Cherry”,B1:B8) and the returns will be 64.

 

# Wildcards:
The function supports wildcards, as seen in the below example

Example #1
In the below example the sum function is configured to sum the character which is beginning with “Ora” in the range of A1:A7. An asterisk (*) matches the rest. So the formula will be =SUMIF(A1:A7,”Ora*”,B1:B7). The returns will be 140.

 

Example #2
SUMIF function is configured to sum values in the range B1:B7 if the corresponding cells in the range A1:A7 contain a character “ge” at the end. Here also an asterisk (*) matches the series of characters. The formula will be =SUM(A1:A7,”*ge”,B1:B7) and returns will be 110.

 

Example #3
In the below format shown that SUMIF function is configured to sum values in the range B1:B7 if the corresponding cells in the range A1:A7 contain character (Coconut) with a number where a question mark (?) matches one character. So the formula will be =SUMIF(A1:A7,”Coconut?”,B1:B7) and the returns will be 90.

 

# Dates:
Use the SUMIF function in excel to sum cells on based on a valid date in another cell or use the date function. Please note that DATE function in the below examples accepts three arguments. i.e. year, month and day.

Example #1
In the below format SUMIF function is configured to sum the cell which is before June 12^th, 2020. The formula will be =SUM(A1:A7,”<”&DATE(2020,6,12),B1:B7). The returns value will be 180.

 

Example #2
The best way to use SUMIF with dates is to refer to a valid date in another cell (D6) which is greater than or equal to March 18^th, 2020. The formula will be = SUMIF (A1:A7 ,” >=” &D6, B1:B7) . The returns will be 85.

 

 

 

 

COUNT Function in Excel

Count is generally a function used to count number of Cells in Excel and returns the count of values that are numbers, generally cells that contains numbers.

Count function helps us count cells in excel. There are three different of counts in Excel. We will now discuss following tropic which is related to Count Function

1) How COUNT Function works in Excel?

2) How the COUNTA function works in Excel?

3) How COUNTBLANK function works in Excel?

 

How COUNT Function works in Excel?

The COUNT function will count cells that contain numbers. Let’s start with the most basic function COUNT.

 

Syntax
=COUNT(value1,[value2],…)

 

Arguments

Value1- An Item, cell reference, or range.
Value2- (Optional) An item, Cell reference, or range.

 

Example #1- Range

The function is counting the number of cells in a specific range. In the example shown, COUNT is set up to count numbers in the range C4:C11. So the formula will be =COUNT(C4:C11). COUNT returns 4, since there are 4 numeric value in the range C4:C11. All others cell will be ignored (texts, blanks)

Example #2- Random

The example below shows COUNT with 3 random value. Two of the value is a number and one is text. So formula will be =COUNT(100,50,Banana), so count returns 2.

 

How the COUNTA Function works in Excel?

If you want to count cells with any type of data (numbers, text, logic values, error value), simple replace COUNT by COUNTA. This will count all cells that are not blank.

 

Syntax
=COUNTA(value1,[value2],…)

 

Arguments

Value1- An Item, cell reference, or range.
Value2- (Optional) An item, Cell reference, or range.

 

In the below example we can see that by the using of COUNTA function we get the range (C4:C11) counts return which is 7. COUNTA does not count cells that are completely empty.

How COUNTBLANK function works in Excel?

The COUNTBLANK function will count cells that are empty. Cells that contain text, number, error, etc. are not counted. The syntax is =COUNTBLANK(range).

If we want to calculate the how many blanks cells in B3:F7 range. Then the formula will be =COUNTBLANK(B3:F7). It returns 5.

Note-

COUNT and COUNTBLANK both will count cells with formula that look empty, If the formula result is an empty string (“”). The empty string is treated as text and counted.

 

Sum Function in Excel

Sum function in excel is an inbuilt function which is used to aggregate value from a selection of columns or rows from your selected range.Most of the times you’ll use the SUM function to sum a range of cells.

 

 

Sum Formula in Excel:

The formula for the SUM function is as follows:

AutoSum:

Use AutoSum or press ALT+= to quick sum or row numbers

 

 How to use the SUM Function in Excel?

Example 1:

Most of the time we will use SUM function in excel to Sum a range of Cells. In the below format we can see in Column D contains half-yearly sale vales; here we are taking first and last cell in a range of cells to added up. We need to select a range from cell D4 to D9, i.e. =SUM(D4:D9)

 

Example 2:

We can do a sum of entire column also. In the below format we have a town in the column A and sale of Feb in column B. If we want to calculate entire Feb cell then we use the formula as below. i.e. =SUM(B:B)

 

Example 3:

In this example we can see that Sum function is using to sum non-contagious cell by using Comma (,). If we want to adding sale of Jan, Mar and June then we need to take the cell of D4, D6, and D9 and the formula will be SUM(D4,D6,D9). In the below format if we need to calculate some selective cell then below formula is working.

 

 Example 4:

In this example if we want to sum function for fraction value “10/5”,”8/2”, “6/3” &”12/4” then the formula will be SUM (10/5,8/2,6/3,12/4)

The Sum function return value is 11         

 

Example 5:

In the previous examples we have added only one range one time but in the below example we will add up multiple range of cells.

D2:D5 represents Jan Sale
E2:E5 represents Feb Sale
F2:F5 represents Mar sale

If we want 1^stQtr Sale, then we need to sum up sale of D2 to F5. The formula will be =SUM(D2:D5,E2:E5,F2:F5)

 

We can use another formula to add this range. i.e. =SUM(D3:F3)