Advanced Excel

Advanced Excel Formulas

• SUMIF
• COUNTIF
• AVERAGEIF
• SUMIFS
• SUMPRODUCT
• INDEX
• MATCH
• LOOKUP 
 SUMIF and COUNTIF

• The SUMIF and COUNTIF functions allow you

to conditionally sum or count cells based on
a single condition
• Syntax:
• =SUMIF(criteria_range, criteria, sum_range)
• =COUNTIF(criteria_range, criteria)
 Example
• To count cells based on one criteria (for
example, greater than 9)
 Example
• To sum cells based on one criteria (for
example, greater than 9), use the following
SUMIF function (two arguments)
 Excel AVERAGEIF Function

• The Excel AVERAGEIF function calculates the

average of numbers in a range that meet
supplied criteria.
• AVERAGEIF criteria can include logical
operators (>,<,<>,=) and wildcards (*,?) for
partial matching.
• Syntax 
• =AVERAGEIF (range, criteria, [average_range])
Example
• =AVERAGEIF(C5:C15,">0") //
price greater than $0 =
AVERAGEIF
(C5:C15,">200000") // price
greater than $200k =
AVERAGEIF
(D5:D15,">=2",C5:C15) // 2+
bedrooms =AVERAGEIF
(D5:D15,">=3",C5:C15) // 3+
bedrooms
 Few More Examples
• For example, to average values in B1:B10 when values
in A1:A10 equal "red", you can use a formula like this:
• =AVERAGEIF(A1:A10,"red",B1:B10) // average "red"
only
• Value from another cell
– A value from another cell can be included in criteria using 
concatenation.
– In the example below, AVERAGEIF will return the average
of numbers in A1:A10 that are less than the value in cell
B1. Notice the less than operator (which is text) is
enclosed in quotes.
– =AVERAGEIF(A1:A10,"<"&B1) // average values less than
B1
 Few More Examples
• Wildcards
– The wildcard characters question mark (?), asterisk(*)
can be used in criteria.
– A question mark (?) matches any one character and
an asterisk (*) matches zero or more characters of
any kind. 
– For example, to average cells in a B1:B10 when cells in
A1:A10 contain the text "red" anywhere, you can use
a formula like this:
– =AVERAGEIF(A1:A10,"*red*",B1:B10) // contains
"red“
 SUMIFS
• It is used to calculate the sum of values that
meet any criteria.
• SUMIFS function – Syntax :

 
 Example

Another example:
=SUMIFS(D3:D16,B3:B16,"Spit Bomb",C3:C16,"*th")  to get sum of spit bombs sold in North and South
 Using SUMIFS() with tables

• When using with tables, Structural references  ie TableName[Column Name]

notation can be applied to specify the criteria columns.
 SUMIFS Examples

• Sales for Blow Torch in West

– =SUMIFS(acme[Sales], acme[Product], "Blow Torch", acme[Region], "West")
• Total Sales above 150 in East
– =SUMIFS(acme[Sales], acme[Sales],">150",acme[Region],"East")
• Sales of North for all excluding Pod Gun
– =SUMIFS(acme[Sales], acme[Region],"North",acme[Product],"<>Pod Gun")
• Sales of all products that contain letter B
– =SUMIFS(acme[Sales], acme[Product], "*B*")
 Using SUMIFS() with Date & time values

• Special operators like >, <, =, <> can be applied to specify a date
range.
• For example, to count total sales between March 2018 and May
2018, we can use
– =SUMIFS(acme[Sales], acme[Sales Date],">=1-Mar-2018", acme[Sales Date], "<=31-May-2018")
 Structural References

• A special syntax for referencing Excel Tables.

• Structured references work like regular cell references in
formulas, but they are easier to read and understand.
 SUMPRODUCT formula

• It takes 1 or more arrays of numbers and gets the sum of

products of corresponding numbers.
• The syntax is =SUMPRODUCT (list 1, list 2 ...)
• Example:
• if you have data like {2,3,4} in one list and {5,10,20} in
another list, and if you apply SUMPRODUCT, you will get
120 (because 2*5 + 3*10 + 4*20 is 120).
 SUMPRODUCT formula
• Used to  calculate the total amount spent.
 Advance use of SumProduct
• the double negative (- -) is to
force Excel to convert the TRUE
and FALSE values into 1’s and
0’s.
Advance use of SumProduct
 VLOOKUP
• An Excel function to look up data in a table organized vertically.
• VLOOKUP supports approximate and exact matching, and wildcards (* ?)
for partial matches. 
• Lookup values must appear in the first column of the table passed into
VLOOKUP.
 VLOOKUP
• Purpose 
– Lookup a value in a table by matching on the first column
• Return value 
– The matched value from a table.
• Syntax 
– =VLOOKUP (value, table, col_index, [range_lookup])
• Arguments 
– value - The value to look for in the first column of a table.
– table - The table from which to retrieve a value.
– col_index - The column in the table from which to retrieve a
value.
– range_lookup - [optional] TRUE = approximate match
(default). FALSE = exact match.
 VLOOKUP Examples
• Using the Order number in column B as a lookup value, VLOOKUP can get the
Customer ID, Amount, Name, and State for any order. For example, to get the
customer name for order 1004, the formula is:
• =VLOOKUP(1004,B5:F9,4,FALSE) // returns "Sue Martin"
 VLOOKUP Examples
• =VLOOKUP(H3,B4:E13,2,FALSE) // first name
=VLOOKUP(H3,B4:E13,3,FALSE) // last name
=VLOOKUP(H3,B4:E13,4,FALSE) // email address
 VLOOKUP Examples:Approximate Match
• The Boolean TRUE (fourth argument) tells the VLOOKUP function to
return an approximate match. If the VLOOKUP function cannot find the
value 85 in the first column, it will return the largest value smaller than 85.
In this example, this will be the value 80.
 VLOOKUP with Multiple criteria

• A helper column is used to join multiple fields together.

• =C5&D5 // helper column
• =VLOOKUP(H4&H5,B5:E13,4,0) //formula
 First Match

• If the leftmost column of the table contains duplicates, the VLOOKUP

function matches the first instance.
Vlookup is Case-insensitive
 Multiple Lookup Tables
• 1. Create two named ranges: Table1 and Table2.

• 2. Select cell E4 and enter the VLOOKUP function shown below.

 Multiple Lookup Tables

• 3. Select cell E4, click on the lower right corner

of cell E4 and drag it down to cell E10.
 Excel INDEX Function
• The Excel INDEX function returns the value
at a given position in a range or array
• Purpose 
– Get a value in a list or table based on location
• Return value 
– The value at a given location.
• Syntax 
– =INDEX (array, row_num, [col_num],
[area_num])
• Arguments 
– array - A range of cells, or an array constant.
– row_num - The row position in the reference or
array.
– col_num - [optional] The column position in the
reference or array.
– area_num - [optional] The range in reference that
should be used.
 INDEX Function
Examples
• The formulas below show how INDEX can be
used to get a value:
• =INDEX(A1:B5,2,2) // returns value in B2
=INDEX(A1:B5,3,1) // returns value in A3
• INDEX can be used to return entire columns or
rows like this:
• =INDEX(range,0,n) // entire column
=INDEX(range,n,0) // entire row
 Excel MATCH Function

• MATCH is an Excel function used to locate the position of a lookup

value in a row, column, or table. MATCH supports approximate and
exact matching, and wildcards (* ?) for partial matches.
• Purpose 
– Get the position of an item in an array
• Return value 
– A number representing a position in lookup_array.
• Syntax 
– =MATCH (lookup_value, lookup_array, [match_type])
• Arguments 
– lookup_value - The value to match in lookup_array.
– lookup_array - A range of cells or an array reference.
– match_type - [optional] 1 = approximate(exact or next smallest)
(default), 0 = exact match, -1 = exact or next largest(approximate).
Excel MATCH Function
 Excel MATCH Function

Match type Behavior Details

MATCH finds the largest
value less than or equal
1 Approximate to lookup value. Lookup array
must be sorted
in ascending order.
MATCH finds the first
value equal to lookup value.
0 Exact
Lookup array does not need to
be sorted.
MATCH finds the smallest
value greater than or equal
-1 Approximate to lookup value. Lookup array
must be sorted
in descending order.
When match type is omitted, it
Approximate defaults to 1 with behavior as
explained above.
Exact match
Approximate match
Wildcard match
Index and Match
 Excel XLOOKUP Function
• XLOOKUP supports approximate and exact matching, wildcards (* ?) for
partial matches, and lookups in vertical or horizontal ranges.
• Purpose 
– Lookup values in range or array
• Return value 
– Matching value(s) from return array
• Syntax 
– =XLOOKUP (lookup, lookup_array, return_array, [not_found], [match_mode],
[search_mode])
• Arguments 
– lookup - The lookup value.
– lookup_array - The array or range to search.
– return_array - The array or range to return.
– not_found - [optional] Value to return if no match found.
– match_mode - [optional] 0 = exact match (default), -1 = exact match or next
smallest, 1 = exact match or next larger, 2 = wildcard match.
– search_mode - [optional] 1 = search from first (default), -1 = search from last, 2
= binary search ascending, -2 = binary search descending.
Excel XLOOKUP Function
 Excel XLOOKUP Function
• Match type
Match type Behavior
Exact match. Will return #N/A if
0 (default)
no match.
Exact match or next smaller
-1
item.
1 Exact match or next larger item.
2 Wildcard match (*, ?, ~)
• Search mode
Search mode Behavior
1 (default) Search from first value
Search from last value
-1
(reverse)
Binary search values sorted
2
in ascending order
Binary search values sorted
-2
in descending order
Example #1 - basic exact match
Example #2 - basic approximate match
Example #3 - multiple values
Example #5 - not found message
 Exercise
The following table details the revenue by bank account number

Calculate the total revenue from "Gold" accounts in the State of NY

Account # Type State Revenue

1 Gold NY 492
2 Silver PA 124
3 Gold NJ 555
4 Gold NY 100
5 Bronze NY 8
6 Bronze MA 201
7 Gold NY 20
8 Silver PA 43
9 Gold PA 108
10 Bronze NJ 172
 Exercise
Create a lookup table containing scores and ratings based on the following
categories:
0 - 4        =    Pooh
5 - 9        =    Could do better
10 - 14    =    Doing better
15 or more    =    Tiggerific
Create a VLOOKUP function to calculate a rating for each player as follows: