There are total over 330 of the Excel worksheet functions. In this section, I will only show you most of the commonly used functions, with many useful examples of multi-nested functions in very complex situations. I have excluded the engineering and financial functions.

DCOUNT, DCOUNTA, DSUM, DPRODUCT, DMAX, DMIN, DAVERAGE, DSTDEV, DSTDEVP,
DVAR, DVARP, DGET

These are the more complex versions of the basic functions like sum, product, max, min, average, count, counta, and are known as database functions. These functions are used to interrogate lists of data held on a worksheet. For selected rows of data, these functions will perform their processing only on records which meet specified criteria.

Functions of SUMPRODUCT, SUMIF, COUNIF, SUBTOTAL, ROUND, ROUNDUP, ROUNDDOWN, ABS, INT

SUMPRODUCT(array1,array2,array3, ...)
Array1, array2, array3, ... are 2 to 30 arrays whose components you want to multiply and then add.

Syntax are:
SUMPRODUCT((array1=condition1)*(array2=condition2)*(array3)) ,or
SUMPRODUCT((array1=condition1)*(array2=condition2),(array3))
SUMIF(range,criteria,sum_range)
COUNTIF(range,criteria)
SUBTOTAL(function_num, ref1, ref2, ...)

General Array Formula Rules
▪ Array formula must be entered using CTRL+SHIFT+ENTER. The parentheses {} must not be entered manually.
▪ You cannot create or use an array that uses a whole column of cells.
▪ Array formulas cannot handle arrays with more than two dimensions.
▪ Some worksheet functions cannot accept array data types within certain parameters. Examples include COUNTIF(), SUMIF(), COUNTBLANK(), CONCATENATE() and database functions. Only certain functions can aggregate arrays: these include SUM(), COUNT(), AVERAGE(), MAX(), MIN(), PRODUCT(), and TRANSPOSE().
▪ You cannot share an Excel workbook that contains a Array formula that is linked to another workbook.
▪ You cannot create more than 65,472 Array formulas that refer to another worksheet.
▪ You cannot enter an Array formula into a cell if it has already been merged.
▪ SUMPRODUCT() and LOOKUP() are able to convert references to arrays without the need for CTRL+SHIFT+ENTER.

Array Ranges Rules
▪ You have to select the range of cells to hold your results before you input the Array formula.
▪ You cannot change the contents of an individual cell in an array range.
▪ You cannot move or delete part of an array range.
▪ You cannot insert blank cells into an array range.
▪ An array range cannot contain merged cells.

Advantages Of Working With Array Formulas
▪ Array Formulas can perform complex calculations which are not possible with standard formulas.
▪ Array Formulas condense multiple calculations into ONLY ONE CELL thereby reducing the size of a workbook.
▪ A range array calculates more quickly than using individual formulas in each cell inside the range.

Disadvantages Of Working With Array Formulas
▪ Using too many array formulas will reduce the calculation speed of the application.
▪ Other database functions and pivot tables can be your alternatives.

Here is one example [see SAMPLE] of nested SUMPRODUCT function I used together with IF, MID, FIND, LEFT, COUNTIF and so on. It finds the quantities by differentiated categories, multiply them by unit costs, then make a summary in just ONLY one cell. Why need so many columns to calculate your result, and wasting time?  DOWNLOAD to see it and learn it.

The following table of data is what I am gonna go through with you on the many examples of SUMPRODUCT, SUMIF AND COUNTIF. You can also DOWNLOAD this worksheet with all the formulas.

To perform an Array Formula, press F2, then press Ctrl+Shift+Enter. The braces {} will be then entered automatically.

SUMPRODUCT((array1="criteria1")*(array2="criteria2")*(array3)) returns the sum of the data in the array3 where the crietria in arrays one and two are met. This can be expanded further.

Having invalid information (eg text) in the array can be ignored using the double unary operator:
SUMPRODUCT(--(array1="criteria1")*(array2="criteria2")*(array3)).

We can coerce boolean values into 1/0 integers such as : --FALSE = 0 and --TRUE = 1 and these are often used with SUMPRODUCT (i.e. TRUE = 1, FALSE = 0) Arrays must be the same dimensions. Arrays cannot be a whole column, but can be a whole row.

The multiplication operator ' * ' mimics AND. To mimic OR you need the addition operator ' + '. The ' * ' operator won't work on a single array. You can use many variations: '1*', '*1', '^1', '+0' or '--'

Return To Top

Note : Formula result with { } means it is a array formula, and must be entered with pressing Ctrl+Shift+Enter

Format of SUMPRODUCT

This syntax is what we first learnt of:
=SUMPRODUCT((array1=condition1)*(array2=condition2)*(array3))

But, we could also use this:
=SUMPRODUCT((array1=condition1)*(array2=condition2),(array3))
which works as the '*' operator is only required to coerce the conditional arrays that resolve to TRUE/FALSE into 1/0 integers.

As it the use of a arithmetic operator that coreces the TRUE/FALSE values to 1/0, we could use many different operators and achieve
the same result. Thus, it is also possible to coerce each of the conditional arrays individually by multiplying them by 1.
=SUMPRODUCT((array1=condition1)*1,(array2=condition2)*1,(array3))
or
=SUMPRODUCT(1*(array1=condition1),1*(array2=condition2),(array3))
or by raising to the power of 1,
=SUMPRODUCT((array1=condition1)^1,(array2=condition2)^1,(array3))
or by adding 0,
=SUMPRODUCT((array1=condition1)+0,(array2=condition2)+0,(array3))
or
=SUMPRODUCT(0+(array1=condition1),0+(array2=condition2),(array3))
or even by using the N function,
=SUMPRODUCT(N(array1=condition1),N(array2=condition2),(array3))

To preserve SUMPRODUCT arrays with multiple conditions that are separated by the comma, you can use -- on both conditions like:
=SUMPRODUCT(--(B2:B11>0),--RIGHT(A2:A11,3))

If you multiply two arrays of TRUE/FALSE, that would coerce to 1/0 values which are then summed, and you could then use the ' * '
=SUMPRODUCT((I2:I11="SALES")*(L2:L11>300))

Any further, final, array of values can use the same operator, or could revert to comma. So your formula can be written as:
=SUMPRODUCT(--(LEFT(A2:A11,1)="A"),--(MID(A2:A11,2,1)<>"A"),MID(A2:A11,3,3)*1)
or
=SUMPRODUCT(--(LEFT(A2:A11,1)="A"),--(MID(A2:A11,2,1)<>"A"),--MID(A2:A11,3,3))
or
=SUMPRODUCT((B2:B11>1)*(E2:E11>2)*(C2:C11))
or
=SUMPRODUCT((B2:B11>1)*(E2:E11>2),(C2:C11))

IF, ISNUMBER, AND, OR, NOT, SEARCH, FIND, MID, LEFT, RIGHT, LEN, MAX,
SUBSTITUTE, REPLACE, TEXT, TRIM, CONCATENATE, LOWER, UPPER, PROPER,
CHAR, CODE, VALUE, EXACT, DOLLAR, T, FIXED

FIND(find_text,within_text,start_num)
      start_num specifies the character at which to start the search. The first character in within_text is character number 1
SEARCH(find_text,within_text,start_num)
      start_num is the character number in within_text at which you want to start searching.
SUBSTITUTE(text,old_text,new_text,instance_num)
      Instance_num specifies which occurrence of old_text you want to replace with new_text
REPLACE(old_text,start_num,num_chars,new_text)

Note: when using SEARCH or FIND to obtain True (found) or False (not found) it will be necessary to test for ISNUMBER to eliminate the return of "#VALUE!" if not found.

You can also DOWNLOAD this demo worksheet here.

Return To Top

The following list describes the text values CELL returns when info_type is "format", and reference is a cell formatted with a built-in number format.

      MATCH, OFFSET, INDIRECT, VLOOKUP, ROWS, MOD, ROW, INT,

MATCH(lookup_value,lookup_array,match_type)
      Returns the relative position of an item in an array that matches a specified value in a specified order.
          If match_type is 1, MATCH finds the largest value that is <= lookup_value. Lookup_array must be in ascending order.
          If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.
          If match_type is -1, MATCH finds the smallest value that is >= lookup_value. Lookup_array must be in descending order.
          If match_type is omitted, it is assumed to be 1.

OFFSET(reference, rows, cols, height, width)
      Reference is where you want to base the offset. Reference must refer to a cell or range of cells (otherwise it returns #VALUE!)
      Rows is the number of rows, up or down, that you want the Reference to point to.
      Cols is the number of columns, left or right, that you want the Reference to point to.
      Height=no. of rows you want the returned reference (must be +ve)
      Width=no. of columns you want the returned reference (must be +ve)

INDEX(reference,row_num,column_num,area_num)
      Returns the reference of the cell at the intersection of a particular row and column.
      Row_num is the number of the row in reference from which to return a reference.
      Column_num is the number of the column in reference from which to return a reference.
      Area_num selects a range in reference from which to return the intersection of row_num and column_num.
         The first area selected or entered is numbered 1, the second is 2, and so on. If area_num is omitted, INDEX uses area 1.

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
      Lookup_value - the value to search in the first column of the table array.
      Table_array - two or more columns of data.
      Col_index_num - the column number in table_array from which the matching value must be returned.
      Range_lookup - a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:
           a) If TRUE or omitted, an exact or approximate match is returned.
           b) If an exact match is not found, the next largest value that is less than lookup_value is returned.
               (the values in the first column of table_array must be placed in ascending sort order).
           c) If FALSE, VLOOKUP will only find an exact match. In this case, the values in the first column of table_array do not need to be sorted.
               If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used.
               If an exact match is not found, the error value #N/A is returned.

ADDRESS(row_num,column_num,abs_num,a1,sheet_text)
      Creates a cell address as text, given specified row and column numbers.
      Row_num is the row number to use in the cell reference.
      Column_num is the column number to use in the cell reference.
      Abs_num Returns this type of reference
          1 or omitted Absolute
          2 Absolute row; relative column
          3 Relative row; absolute column
          4 Relative
      A1 is a logical value, If a1 is TRUE or omitted, ADDRESS returns an A1-style reference; if FALSE, ADDRESS returns an R1C1-style reference.
      Sheet_text is text specifying the name of the worksheet to be used as the external reference. If sheet_text is omitted, no sheet name is used.

ROWS(array)
      returns the number of rows in a reference or array.

MOD(number,divisor)
      returns the remainder after number is divided by divisor. The result has the same sign as divisor.

ROW(reference)
      returns the row number of a reference.

INT(number)
      rounds a number down to the nearest integer.


You can DOWNLOAD this demo worksheet.

Return To Top

Return To Top

This site was created in February 2007
by William Tan