NOT Function

The NOT Function returns TRUE if the argument is false and FALSE if the argument is true.

=NOT(logical)

OR

The OR function returns TRUE if any argument is true and returns FALSE if all arguments are false.


=OR(logical1,logical2)

AND function

The AND function accepts two or more logical tests and displays TRUE if all conditions are true or FALSE if any of the conditions are false.

=AND(logical1,logical2)

COUNTIFS

COUNTIFS function counts the number of cells in a range that meet multiple criteria.

=COUNTIFS(C$2:C$13,"Account Rep",B$2:B$13,"Atlanta")

=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2...)

AVERAGEIFS

AVERAGEIFS function calculates the average value of cells in a range that meet multiple criteria.

This function calculates the average value in the Salary range if the Title range contains Account Rep and if the Location range contains Atlanta.
=AVERAGEIFS(E$2:E$13,C$2:C$13,"Account Rep",B$2:B$13,"Atlanta")

=AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2, criteria2...)

SUMIFS

The SUMIFS function calculates the total value of cells in a range that meet multiple criteria.

=SUMIFS(E$2:E$13,C$2:C$13,"Account Rep",B$2:B$13,"Atlanta")

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2...)

SUM_range
The sum_range argument designates the cells containing values to add if the condition is met. In the SUMIFS function, the sum_range argument is the first argument, instead of the last argument as in the SUMIF function.

Criteria_range1
The range argument specifies the first range of cells you want to evaluate to see if the values meet a particular condition. The range must contain values, range names, arrays, or references that contain numbers, dates, or text.

Criteria1. The criteria1 argument specifies the condition for the criteria_range1 argument that imposes limitations on what values are summed.

Criteria_range2. The range argument specifies the second range of cells you want to evaluate to see if the values meet a particular condition. The range must contain values, range names, arrays or references that contain numbers, dates or text.

Criteria2. The criteria2 argument specifies the condition that imposes limitations on what values are summed.

COUNTIF

COUNTIF function is similar to COUNT function except that it calculates the number of cells in a range that meet a specific condition you specific condition you specify instead of calculating the count of an entire range.

=COUNTIF(C2:C13,"Account Rep")

=COUNTIF(range, criteria)

AVERAGEIF

AVERAGEIF function calculates the average, or arithmetic mean, of all cells in a range that meet a specific condition.

=AVERAGEIF(C2:C13,"Account Rep", E2:E13)
=AVERAGEIF(range, criteria, average_range)

Range and Criteria meanings are the same as SUMIF. The average_range argument specifies the range containing values that you want to average if the condition is met.

SUMIF Function

SUMIF Function calculates a sum of values in a range only when related data meet a specific condition instead of calculating the sum of an entire range.

For example, if you want to calculate the total salaries for all Account Reps, you cannot use the SUM function because it calculates the total salaries for all employees. However, you can use the SUMIF function to do this.

=SUMIF(C2:C13,"Account Rep", E2:E13)
=SUMIF(range, criteria, sum_range)

Range. The Range argument specifies the range of cells you want to evaluate to see if the values meet a particular condition.
Criteria. The criteria argument specifies the condition that imposes limitations on what values Excel sums. The criteria can be a value, date, text, or another cell containing a value, date, or text.
Sum_range. The sum_range argument designates the cells containing values to add if the condition is met.

Formulas to remember!

=SUM()
=AVERAGE()
=MEDIAN()
=MIN()
=MAX()
=COUNT()
count tallies the number of cells in a range that contain values
=COUNTBLANK()
tallies the number of blank cells in a range
=COUNTA()
tallies the number of cells in a range that are not empty. 
=ABS(number)
displays the absolute (i.e. positive) value of a number
=FREQUENCY(data_array, bins_array) 
counts how often values appear in a given range
=INT(number) 
rounds a value number down to the nearest whole number
=MODE.SNGL(number1,[number2],...)
displays the most frequently occurring value in a list

=PI()
Returns the value of pi that is accurate up to 15 digits
=PRODUCT(number1, [number2],..)
Multiplies all values within the argument list
=RANDBETWEEN(bottom,top)
Generates a random number between two numbers you specify
=RANK.AVG(number,ref,[order])
Identifies a values rank within a list of values; returns an average rank for identical values.

=RANK.EQ(number, ref,[order])
Identifies a values rank within a list of values; the top rank is identified for all identical values.
=ROUND(number,num_digits)
Rounds a value to a specific number of digits. Rounds numbers of 5 and greater up and those less than 5 down.
=SUMPRODUCT(array1,[array2],[array3],...)
Finds the result of multiplying values in one range by the related values in another column and then adding those products.
=TRIMMEAN(array,percent)
Returns the arithmetic average of the internal values in a range by excluding a specified percentage of values at the upper and lower values in the data set. This function helps reduce the effect outliers (i.e. extreme values) have on the arithmetic mean
=TRUNC(number,[num_digits])
Returns the integer equivalent of a number by truncating or removing the decimal or fractional part of the number. For example,=TRUNC(45.5) returns 45.
=IF(logical_test,value_if_true,value_if_false)
=VLOOKUP(Lookup_value,table_array,col_index_number,[range_lookup])
=SUMIF(range,criteria,sum_range)
=AVERAGEIF(range, criteria, average_range)
=COUNTIF(range,criteria)
=SUMIFS(sum_range, critera_range1,criteria1,criteria_range2,criteria2...)
=AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria...)
=COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2...)
=MATCH(lookup_value,lookup_array,[match_type])
=INDEX(array,row_num,[column_num])