Short Courses in Computer Software -Excel No.3
CountIf
SumIf
IF Function
Optional - IF Statements
Nested IF statements.
AND Function
OR Function
NOT Function
“IF”Functions
There are many “If” functions in Excel. The word “if” simply means the formula works from a “criteria” that the user must apply.
CountIf
Counts the number of cells within a range which meets the given criteria
Syntax:COUNTIF(range, criteria)
Example1: COUNTIF(C12:C25,6) - would count all the number 6’s in the range.
Example2: COUNTIF(C12:C25,”Alpha”) - would count all the cells with the word “Alpha” contained in the range or list. Text must have the “Quotation” marks around it.
SumIf
Sometimes you may wish to add numbers, but only some of them, not all. Like the Sum command, SumIf adds the contents of cells but only adds what is specified.
You can work with a single column or dual columns.
A single column formula would be when the information and the criteria are in the same column.
The twocolumn is when the criteria is in one column and the data is in the adjoining column. Using this method the data must be in the same row but the columns do not necessarily need to sit side by side.
Syntax:SUMIF(range,criteria,sum_range)
Rangeis the range of cells containing the criteria you want evaluate.
Criteriais the criteria that defines which cells will be added. This can be a number, text or expression. For example 55, "55", "<55”, "word".
Sum_range are the actual cells to sum.
The cells in sum_range are added only if their corresponding cells in range match the criteria. (Must be used with dual columns)
If sum_range is left blank, all the cells in range are summed. (Can be ignored when using a single column)
Examples of the SUMIF formula could be;
A / B / C / D1 / 100 / Z / 14
2 / 25 / X / 18
3 / 75 / Y / 15
4 / 35 / Z / 16
=SUMIF(A1:A20,"<50") would total the cells that contained entries less than 50 in the range A1:A20
=SUMIF(C1:C20,"Z",D1:D20) would total the cells in the range D1:D20 that align with the entry Z in the range C1:C20
AverageIf
You can also use this method to find the AverageIf
IF Function
You would use the IF function when you want to perform a logical test and return one value if the outcome of the test is True and another value if the outcome of the test is False.
Syntax: If(logical_test, Value_if_true, Value_if_false)
Examples;
As a simple example you could create an IF statement to check the contents of a cell (eg A1). If the content is less than 100 then give an answer of 10 or if it is not less than 100 (or 100 or more than) give an answer of 20.
The IF formula would look like this;
=IF(A1<100,10,20)
You could replace the results with references so the result would give us what is in the existing cells.
The IF formula could look like this;
=IF(A1<100,B5,D20)
You could replace the results with text. For Excel to recognise text you must use Quotation “ ”marks around it.
The IF formula could look like this;
=IF(A1<50, “Fail”, “Pass”)
For a more advanced example, imagine you were evaluating wages. If the employee’s wage is less than $300 per week, a $40 bonus will be added to their wage. If the wage is greater than $300, only a $25 bonus is to be given.
If the wage is in cell B2, the formula is:
=IF(B2<300, B2+40, B2+25)
The following operators can be used in IF statements.
=Equals>=Greater Than or Equals
Greater Than<=Less Than or Equals
Less ThanDoes not Equal
Optional - IF Statements
Nested IF statements.
There may be occasions that you may want to have more than one condition (or Logical_test). You can use more than one IF statement in the same cell. To do this you place the second IF statement where the “Value if false” part is in the formula. The last IF statement will contain the “Value if false” part. Note: don’t forget to close all the brackets.
Syntax: IF(logical_test, Value_if_true, IF(logical_test, Value_if_true, Value_if_false))
Example: Using the above example, if the employee’s wage is less than $200 per week, a $50 bonus will be added to their wage. If the wage is between $200-$299 a $40 bonus will be added to their wage. If the wage is $300 or greater, only a $25 bonus is to be given.
If the wage is in cell B2, the formula is:
=IF(B2<200,B2+50,IF(B2<300,B2+40,B2+25))
There are 3 other functions you can use when you have more than one option. These functions can be incorporated into the IF statement and are as follows.
AND Function
AND is used when there are two or more logical arguments. If all the arguments are True, then AND gives a True result, if one of more of the arguments is false, then AND returns False. AND is often used in conjunction with the IF function in the logical test.
Syntax:And(logical 1, logical 2,...)
Example: =And(2+2=4, 2+3=5) will return True.
Using the above example, imagine the bonus of $40 is only to be given if both the wage is less than $300 and the employee has worked for more than 3 years with the company. If the amount of time the employee has worked is in C2, the formula is:
=IF(AND(B2<300, C2>3),B2+40, B2+25)
OR Function
OR (in comparison to AND) is used when a number of arguments are to be evaluated but only one of them needs to be true to return a True. If all arguments are false, then a false is returned. Once again, OR is often used in conjunction with the IF function.
Syntax: =OR(logical 1, logical 2, ...)
Example: =OR(1+1=3, 2+2=4) will return True. (In comparison if the function had been AND instead of OR, the formula would have returned False).
Using the above example, imagine that the bonus of $40 is to be given to employees who have been working with the company for more than three years or who have less than $300 per week. Anyone else will get a bonus of $25. In this case the formula is:
=IF(Or(B2<300, C2>3), B2+40, B2+25)
NOT Function
The function NOT is used when you want to reverse the value of an argument, that is, make sure it is not equal to one particular value.
=IF(Not(B2<300), B2+40, B2+25)