More Excel Functions

• Functions increase user productivity when working with excel.

• It is much more efficient to use a function to get the a result than

using the formula which will have to reference a lot of cells.

IF Function
• In Microsoft Excel, the IF function is used to check if a condition is
met and then the defined statements will be shown based on the
given condition.
• The objective of the IF function is to Check whether a condition is met, and returns one
value if TRUE, and another one if FALSE.

Syntax: =IF(logical_test, [value_if_true], [value_if_false])

COMPULSORY/
ARGUMENT EXPLANATION
OPTIONAL
Given condition for a cell
logical_test Compulsory
or a range of cells.
Defined statement if the
[value_if_true] Optional
condition is met.
Defined statement if the
[value_if_false] Optional
condition is not met.

OPERATOR DESCRIPTION
= Equal to

<> Not Equal to

> Greater Than

>= Greater Than or Equal to

< Less Than

<= Less Than or Equal to

IF Function to Show Statements Based on Logical Test

• In the dataset, there are two columns containing Sales Target and Sales
Achieved for some products.

• By using the IF function, we’ll check and show statements in Column E if the
achieved sales have met the sales target or not.

• In the output Cell E5, we have to type: =IF(D5>=C5,"Achieved","Not Achieved")

• Press Enter, use Fill Handle to autofill the entire column and will find the
statuses of all sales based on the defined criteria and statements.
Using IF Function to Return Calculated Result
• We’ll use the IF function to find bonuses for some salespersons based on the status of the sales.

• If a salesperson meets the target, then his/her bonus of the salary will be calculated by the
percentage of sales increase.

• Select Cell E5 and type: =IF(D5>=C5,$C$15*(D5-C5)/C5,"Not Applicable")

• Press Enter, autofill the entire column, and we’re done.

• Will find the bonuses for those who have met the sales target and the formula will return with the
statement “Not Applicable” if target sales are not achieved.
Use of Nested IF Function in Excel
• The nested IF formula is widely used to determine the grade letter of a student in an exam.

• In our table, 1st 2 columns consist of subject names and marks out of 100 for each one.

• Column F shows the grading system.

• We’ll find out the letter grade obtained in each subject in Column D.

• In Cell D5, the nested IF formula to find the letter grade will be
=IF(C5>=80,"A",IF(C5>=70,"B",IF(C5>=60,"C",IF(C5>=50,"D","F"))))

• Press Enter, autofill the rest of the cells and will be shown letter grades for all subjects based on the defined
grading system.
IF with AND, OR and NOT Functions
• In the dataset, Columns C, D, E, and F consist of the donor names, donation amounts, mediums of donations
and donation dates respectively.

• By using the IF function before the AND function, we can add some statements based on the return types of
logical values.

• For example, we want to see the names only in Column G who have donated more than $500 before 1 August
2021.

• In the output Cell G5, the related formula will be: =IF(AND(D5>$J$11,F5<$J$12),C5,"")

• Press Enter, autofill the entire column.

• Now by using the OR function inside the IF function, we’ll find out the
names of the donors who have donated through cash or cheque from Column
E.

• In Cell G5, the related formula with IF and OR functions will be:
=IF(OR(E5=$J$11,E5=$J$12),C5,"")

• Press Enter, autofill the entire column.

• In MS Excel, the the NOT function is used to convert the logical
values- TRUE and FALSE into FALSE and TRUE respectively.

• So, by using this function inside the IF function, we can find out the donors
who have not donated through cash.

• In Cell G5, the related formula will be: =IF(NOT(E5=$J$11),C5,"")

• After pressing Enter and auto-filling the rest of the cells, will get the expected
results at once.
IF with ISBLANK, ISTEXT, ISNUMBER and ISLOGICAL Functions

• In Microsoft Excel, ISBLANK, ISTEXT, ISNUMBER and ISLOGICAL are all logical functions through
which we can determine if a cell contains nothing, text string, number string, or a logical value
respectively.

• In the table, we’ll find out in Column C what types of data are lying in Column B.

• In the output Cell C5, we have to type:

=IF(ISTEXT(B5),"Text",IF(ISNUMBER(B5),"Number",IF(ISBLANK(B5),"Blank",IF(ISLOGICA
L(B5),"Logical Value",""))))

• Press Enter, autofill the entire column

IF with DATE Function in Excel

• We can use IF with a the DATE function to determine the due status for tuition fees of the
students.

• Assuming, the deadline for the payment of tuition fees for July is 7/31/2021.

• We’ll find out the status of the students who paid the tuition fees in time and who couldn’t.

• n the output Cell E5, the related formula will be: =IF(C5<=DATE(2021,7,31),"In
Time","Delayed")

• Press Enter, fill down the entire column

SUMIF Function
• In terms of calculating the summation of some numbers sometimes we may need to apply
conditions or criteria.

• MS Excel helps us with these kinds of problems by providing another powerful function
named SUMIF.

• The following image is a quick view of the Excel SUMIF function.

• This function adds the cells specified by a given condition or criteria

• Syntax: =SUMIF (range, criteria, [sum_range])

REQUIRED/
ARGUMENT EXPLANATION
OPTIONAL
The range of cells that we
range Required want to be evaluated by
criteria.
The criteria are in the form
of an expression, a number
criteria Required a text, a function, or a cell
reference, that defines
which cells are to add.
The actual cells to add if we need to
sum range Optional combine cells other than those defined in
the range argument.
Calculating Sum with Numeric Criteria Using SUMIF Function

• Using the SUMIF function, we can calculate the sum with the numeric conditions.

• With the dataset below:

• Firstly, write the formula in the H7 cell like this: =SUMIF(E5:E16,">1000")

• Here, E5:E16 refers to the column of Sales.

• Secondly, press ENTER.

• Eventually, we’ll get the output as $26,700.

Finding Sum with Text Criteria Using SUMIF Function

• We will see how to calculate the sum using text criteria.

• Here our concern is to calculate sales from the dataset where the Category will
be Fruits.

• So, firstly, write the formula in the H8 cell like this =SUMIF(C5:C16,"Fruits",E5:E16)

• Press ENTER, and consequently, the output will be $14,700.

Calculating Sum with Date Criteria

• The SUMIF function is also applicable for using date conditions.

• Say that we want to get the sum of sales of those foods where the date is after 04/01/2021.

• Write the formula in the H8 cell like this: =SUMIF(D5:D16,">"&DATE(2021,4,1),E5:E16)

• Again, press ENTER.

• Eventually, the output is like this.

Calculating Sum with OR Criteria in SUMIF Formula

• OR logic means if any logic or condition is true from the given logic then it will return true.

• We can use this logic using the SUMIF function.

• assume we want to calculate the total sales where Category is Vegetables, or each sale is greater than $1000.

• Write the formula in the H8 cell like this

=SUMIF(C5:C16,"Vegetables",E5:E16)+SUMIF(E5:E16,">1000",E5:E16)

• Plus sign (+) is used for the OR

Applying SUMIF with an Array Criteria

• In the SUMIF function, we use the array argument as a condition.

• An array argument is nothing but an array of some elements in any function’s parameter. Like:
{“A”, “B”, “C”} etc.

• Now here we will count the total sales where Category is Fruits and Dairy using
the SUMIF function.

• So, write the formula in the H8 cell =SUM(SUMIF(C5:C16,{"Fruits","Dairy"},E5:E16))

Using SUMIF with Date Range (Month and Year) Criteria

• We can use the SUMIF function where we need to calculate the sum within a range
of Month and Year.

• In the following dataset, we have column headers as Project, Start Date, Finish Date, Rate Per
Hour, Worked Hour, and Total Bill.

• Suppose, in the C13 cell we need to find out Total Bill.

• Firstly, write the formula in the C13 cell like this. =SUMIF(D5:D10,"="&C12,G5:G10)
COUNTIF Function
• In Microsoft Excel, the COUNTIF function is widely used to count cells with a
given condition or criterion.

• The above screenshot is an overview of the article, representing an application

of the COUNTIFfunction in Excel.

• The objective of the COUNTIF function to count the number of cells within a
range that meet the given condition.
• Syntax: =COUNTIF(range, criteria)

REQUIRED/
ARGUMENT EXPLANATION
OPTIONAL

Range of cells where the criteria

range Required
will be assigned for counting.

Condition or criterion for the

criteria Required
selected range of cells.
Use of COUNTIF Function with Comparison Operator in Excel

• Select the output cell C24 and type: =COUNTIF(F5:F19,C22) Or

=COUNTIF(F5:F19,">70")

• press Enter and the number of participants who have scored more than 70 in
the contest will be shown.
• Can also use COUNTIF with Text criteria, to count blank and non-blank cells,
date criteria, multiple OR criteria etc.

VLOOKUP function
• The VLOOKUP function looks for a given value in the leftmost column of a
given table and then returns a value in the same row from a specified column.

Suppose we have the item serial number, and we would

like to know the item description, we can use the
VLOOKUP function.
• The Syntax of the VLOOKUP function is:
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

• The lookup_value can be a single value or an array of values.

• If you enter an array of values, the function will look for each of the values in
the leftmost column and return the same row’s values from the specified
column. ARGUME REQUIRED/
VALUE
NT OPTIONAL

The value that it looks for is in the leftmost column of the

lookup_value Required
given table. Can be a single value or an array of values.

The table in which it looks for the lookup_value in the

table_array Required
leftmost column.

The number of the column in the table from which a

col_index_num Required
value is to be returned.

Tells whether an exact or partial match of the

[range_lookup] Optional lookup_value is required. 0 for an exact match, 1 for a
partial match. The default is 1 (partial match).
• When the lookup_value is a single value, it searches for the value in the leftmost
column of the given table_array.

• If it finds one, then it moves to the specified number of columns right given
as col_index_num in the same row.

• After moving to the specified number of columns right, it returns the value from the
destination cell.

• In the following figure, the formula is: =VLOOKUP("shane Lee",B6:D10,3,FALSE)

• First, it searches for “Shane Lee” in the leftmost column B of

the table_array B6:D10.

• Then it finds one in cell B8. Then it moves to column 3 (col_index_num) of the
table, in the same row. That is cell D8.

• And then it returns the value from that cell, in this case, it is the salary of Shane Lee,
$22000.00.
• The excel VLOOKUP function can be used to solve several other problems
• It can be used alone or in combination with other functions
 IF Function

• The IF function is used when there is a condition to be evaluated.

• A condition is an expression that either evaluates to true or false.

• The expression could be a function that determines if the value entered in a cell is of
numeric or text data type, if a value is greater than, equal to or less than a specified
value, etc.

• In the example, we will use the IF function to determine if an item is expensive or not.

• Will assume that items with a value greater than 6,000 are expensive and those that
are less than 6,000 are less expensive.
• Put the cursor focus in cell F4

• Enter the following formula that uses the IF function

• =IF(E4<6000,”Yes”,”No”)

• “=IF(…)” calls the IF functions

• “E4<6000” is the condition that the IF function evaluates. It checks the value of cell address E4
(subtotal) is less than 6,000

• “Yes” this is the value that the function will display if the value of E4 is less than 6,000

• “No” this is the value that the function will display if the value of E4 is greater than 6,000

• Press enter and the following will be got.

 Nested IF functions

• A nested IF function is an IF function within another IF function.

• Nested if statements come in handy when we have to work with more than two conditions.

• Say we want to develop a simple program that checks the day of the week.

• If the day is Saturday we want to display “party well”, if it’s Sunday we want to display “time to
rest”, and if it’s any day from Monday to Friday we want to display, remember to complete your to
do list.

• A nested if function can help us to implement the above example.

• The following flowchart shows how the nested IF function will be implemented.
• The formula for the flowchart is as follows.

• =IF(B1=”Sunday”,”time to rest”,IF(B1=”Saturday”,”party well”,”to do list”)).

• “=IF(…,IF(….))” the second IF function is the nested one. It provides further

evaluation if the main IF function returned false.