MS Excel Learning Classes – 8

MS Excel Learning Classes – 8

SUMIF

Adds the cells specified by a given criteria.

Syntax                                SUMIF(range,criteria,sum_range)

Range                  is the range of cells you want evaluated.

Criteria                is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, “32”, “>32”, “apples”.

Sum_range       are the actual cells to sum.

Example

1
2
3
4
5
 
 
A B
Property Value Commission
100,000 7,000
200,000 14,000
300,000 21,000
400,000 28,000
Formula Description (Result)
=SUMIF(A2:A5,”>160000″,B2:B5) Sum of the commissions for property values over 160000 (63,000)

MAX

Returns the largest value in a set of values.    Syntax                                MAX(number1,number2,…)

Number1, number2, …    are 1 to 30 numbers for which you want to find the maximum value.

Example

1
2
3
4
5
6
 
 
 
A
Data
10
7
9
27
2
Formula Description (Result)
=MAX(A2:A6) Largest of the numbers above (27)
=MAX(A2:A6, 30) Largest of the numbers above and 30 (30)

MIN

Syntax                    MIN(number1,number2,…)Returns the smallest number in a set of values.

Number1, number2, …    are 1 to 30 numbers for which you want to find the minimum value.

Example

1
2
3
4
5
6
 
 
 
A
Data
10
7
9
27
2
Formula Description (Result)
=MIN(A2:A6) Smallest of the numbers above (2)
=MIN(A2:A6,0) Smallest of the numbers above and 0 (0)

MOD

Syntax                MOD(number,divisor)Returns the remainder after number is divided by divisor. The result has the same sign as divisor.

Number    is the number for which you want to find the remainder.

Divisor    is the number by which you want to divide number.

Example

1
2
3
4
5
A B
Formula Description (Result)
=MOD(3, 2) Remainder of 3/2 (1)
=MOD(-3, 2) Remainder of -3/2. The sign is the same as divisor (1)
=MOD(3, -2) Remainder of 3/-2. The sign is the same as divisor (-1)
=MOD(-3, -2) Remainder of -3/-2. The sign is the same as divisor (-1)

COUNTIF

Counts the number of cells within a range that meet the given criteria.

Syntax                  COUNTIF(range,criteria)

Range    is the range of cells from which you want to count cells.

Criteria    is the criteria in the form of a number, expression, or text that defines which cells will be counted.

Example

1
2
3
4
5
 
 
 
 
A B
Data Data
apples 32
oranges 54
peaches 75
apples 86
Formula Description (Result)
=COUNTIF(A2:A5,”apples”) Number of cells with apples in the first column above (2)
=COUNTIF(B2:B5,”>55″) Number of cells with a value greater than 55 in the second column above (2)

NOW

Syntax                  NOW( )Returns the serial number of the current date and time. If the cell format was General before the function was entered, the result is formatted as a date.

PRODUCT

Multiplies all the numbers given as arguments and returns the product.

Syntax                  PRODUCT(number1,number2,…)

Number1, number2, …    are 1 to 30 numbers that you want to multiply.

Example

1
2
3
4
 
 
 
A
Data
5
15
30
Formula Description (Result)
=PRODUCT(A2:A4) Multiplies the numbers above (2250)
=PRODUCT(A2:A4, 2) Multiplies the numbers above and 2 (4500)

 

 

Spread the love
About admin 2013 Articles
Please feel free to contact us or send your comments. [email protected]

Be the first to comment

Leave a Reply

Your email address will not be published.