Class notes for SUMIF and AVERAGEIF
Summary of all the functions shown in class so far are below.
| Function | Definition | Number of Arguments | Argument Description | Example |
| =SUM | Adds cells | 1 | range (list or table), cells or numbers | e.g. =SUM(A3:A20) |
| =AVERAGE | Adds cells and divide by the number of cells; computes average | 1 | range (list or table), cells or numbers | e.g. =AVERAGE(A3:A20) |
| =MAX | Finds the maximum value in a range | 1 | range (list or table), cells or numbers | e.g. =MAX(A3:A20) |
| =MIN | Finds the minimum value in a range | 1 | range (list or table), cells or numbers | e.g. =MIN(A3:A20) |
| =COUNT | Count the numbers in a range | 1 | range (list or table), cells or numbers | e.g. =COUNT(A3:A20) |
| =COUNTA | Counts cells that are not empty; counts cells with any value inside of them | 1 | range (list or table), cells or numbers | e.g. =COUNTA(A3:A20) |
| =COUNTIF | Counts cells that meet a criteria (condition) | 2 | Part 1=Range; Part2 = Condition (criteria) | e.g. COUNTIF(A3:A20,”M”) |
| =COUNTIFS | Counts cells that meet multiple conditions | 4 | Part 1 = Range 1; Part 2= Condition 1; Part 3 = Range 2; Part 4= Condition 2, etc | e.g. COUNTIFS(A3:A20,100,B3:B20,”<10″) |
| =IF | Performs a calculation if a conditional statement is true | 3 | Part1 = logical test (Conditional Statement); Part 2 = Calculation if the statement is true; Part 3 = Calculation if the statement is false. | e.g. =IF(5=4,”TRUE”,”FALSE”) |
Excel Function: a predefined formula in excel that performs a calculation.
Begin writing an excel formula with the equal symbol (=). This is a reserved character that lets the program know that you will be writing a function.
COUNT FUNCTION
The count functions in excel are statistical functions that count specific information in a table.
1. =COUNT (list or cells)
This function will count the numbers in a list.
2. =COUNTA (list or cells)
This function counts non-empty cells.
3. =COUNTIF(list, criteria)
This function will count cells that meet a condition.
BASIC NOMENCLATURE
Each item in an excel document is refereed to as a cell.
Each Row in excel is named using numbers.
Each Column is names using letters.
Each cell has a unique name and is named by the column number followed by the row number: e.g. A4, E43, etc.