Home
Using HCL Connections Docs 2.0 CR3
Welcome to the HCL Connections Docs 2.0 CR3 documentation, where you can find information about HCL Docs 2.0 CR3. HCL Docs is a collaborative productivity suite that enables you to work on documents, spreadsheets and presentations together in the cloud.
Using HCL Docs
HCL Connections™ Docs is a new office productivity suite for working on documents, spreadsheets and presentations together. It provides real-time co-editing, allowing users to work on documents individually or collaboratively. Comment on a document, or even participate in a discussion. All this without the need of desktop software.
Working with spreadsheets
Calculate, analyze, and manage your data. Several categories of functions are available to create formulas to perform complex calculations on your data.
Using formulas in calculations
This section provides background information on formulas and functions, and organizes the functions in alphabetical order or by category to make it easy to find related functions.
List of functions
Use these functions to create formulas for spreadsheet calculations.
SUM function
SUM adds all the numbers in a range of cells.

Using HCL Connections Docs 2.0 CR3
Welcome to the HCL Connections Docs 2.0 CR3 documentation, where you can find information about HCL Docs 2.0 CR3. HCL Docs is a collaborative productivity suite that enables you to work on documents, spreadsheets and presentations together in the cloud.
- Using HCL Docs
  HCL Connections™ Docs is a new office productivity suite for working on documents, spreadsheets and presentations together. It provides real-time co-editing, allowing users to work on documents individually or collaboratively. Comment on a document, or even participate in a discussion. All this without the need of desktop software.
  - Accessibility features for HCL Docs
    Accessibility features assist users who have a disability, such as restricted mobility or limited vision, to use information technology content successfully.
  - Supported browsers
    See what Web browsers are supported by HCL Connections™ Docs.
  - What's new in HCL Docs 2.0
    Learn about new features and functions in HCL Docs 2.0.
  - Publishing a version of a file
    In order for readers to see a draft created in HCL Connections Docs, or edits made in Docs, a new version of the file must be published. As of August 2015, if a file is being created or edited in Docs for the first time, publishing is set to occur automatically. If a file was previously created or edited in Docs, you must either enable the Publish Automatically command in that file, or publish each new version manually.
  - What if I have a file conflict?
    A person can decide to upload a new version of a file while another person is editing the file online or has saved an online file that is not yet published. The new uploaded version is stored in Files as its own version number, and you are warned of the conflict the next time you try to edit the file from the Files list.
  - Reverting to last version
    When you are editing in HCL Connections™ Docs, if you are not satisfied with the current version of your document, you can revert to the last version that was published. You can choose to save or discard your current edits before you revert to the last version of a document.
  - Viewing a file by using the viewer
    You can view an uploaded document, spreadsheet, presentation, or PDF file while you are working in Files.
  - Accessibility features for HCL Docs
    Accessibility features assist users who have a disability, such as restricted mobility or limited vision, to use information technology content successfully.
  - Working with documents
    Work on a document by yourself or collaborate on files with your colleagues. You can comment on a document, or even participate in a discussion. All this in real-time. Upload an existing Open Document Format or Microsoft™ Word file or create a document to begin.
  - Working with spreadsheets
    Calculate, analyze, and manage your data. Several categories of functions are available to create formulas to perform complex calculations on your data.
    - Creating and importing spreadsheets
      Learn how to create spreadsheets and work with existing spreadsheets in your web browser.
    - File compatibility and exporting spreadsheets
      Find out which file types you can use with HCL Connections™ Docs spreadsheets.
    - Working with spreadsheets in Content Navigator
      If your organization uses IBM Content Navigator as its file repository, see the following topics to learn how to create, import, check in, and save spreadsheet files in the repository.
    - Sharing spreadsheets
      You can make a spreadsheet public for anyone to read, or share it with specific people and communities. You can allow people to edit a spreadsheet and even share it with others. You can also change or delete access levels after you share a spreadsheet.
    - Collaborating on spreadsheets in real time
      You can collaborate on a spreadsheet in real time with others as a team. You can co-edit with other editors in real time, and create comments and discussions.
    - Changing the look of your spreadsheets
      Simple enhancements to the way your spreadsheets look can improve their effectiveness.
    - Managing sheets in spreadsheets
      You can insert a sheet into a spreadsheet, and then move, rename, hide and show the sheet. For an overview of the basics and for tasks that you might need help with, see the following topics.
    - Formatting numbers
      A number can look different from the actual value entered in a cell, depending on the number format. Number formats differentiate one kind of data from another; for example, currency from percentages. Number formats affect only how HCL Connections™ Docs displays numbers, not how it stores or calculates numbers.
    - Entering and editing data
      Most editing tasks in HCL Connections™ Docs are easy to do on your own. For an overview of the basics and for tasks that you might need help with, see the following topics.
    - Working with images in spreadsheets
      You can insert an image into a spreadsheet, and then resize and move the image.
    - Working with charts in spreadsheets
      You can insert a chart into a spreadsheet, and then resize, move, and delete the chart.
    - Managing data ranges
      Learn how to sort data in a sheet or range and reference data that is on a different sheet.
    - Using formulas in calculations
      This section provides background information on formulas and functions, and organizes the functions in alphabetical order or by category to make it easy to find related functions.
      - About formulas and functions
        You enter a formula into a sheet to perform a calculation on numbers, text, or other formulas. When you use formulas, your data becomes dynamic. Functions are built-in formulas that perform specialized calculations automatically. You can use a function by itself as a formula, or combine it with other functions and formulas. Use operators to indicate how the parts of a formula are related.
      - List of functions
        Use these functions to create formulas for spreadsheet calculations.
        Grouped by category
        These functions are sorted by category to make it easy to find related functions.
        ABS function
        ABS returns the absolute value of a number.
        ACOS function
        ACOS returns the inverse trigonometric cosine of a number.
        ACOSH function
        ACOSH returns the inverse hyperbolic cosine of a number.
        ACOT function
        ACOT returns the inverse cotangent of a number. The angle is measured in radians.
        ACOTH function
        ACOTH returns the inverse hyperbolic cotangent of a number.
        ADDRESS function
        ADDRESS returns a cell address (reference) as text, according to the specified row and column numbers. Optionally, you can determine how the address is interpreted. The address can be interpreted as an absolute address (for example, $A$1), a relative address (as A1), or as a mixed form (A$1 or $A1). You can also specify the name of the sheet.
        AND function
        AND returns TRUE if all arguments are TRUE. If one of the elements is FALSE, this function returns the FALSE value.
        ASIN function
        ASIN returns the inverse trigonometric sine of a number.
        ASINH function
        ASINH returns the inverse hyperbolic sine of a number.
        ATAN function
        ATAN returns the inverse trigonometric tangent of a number.
        ATAN2 function
        ATAN2 returns the inverse trigonometric tangent of the specified x- and y-coordinates.
        ATANH function
        ATANH returns the inverse hyperbolic tangent of a number.
        AVERAGE function
        AVERAGE returns the average of the arguments.
        AVERAGEA function
        AVERAGEA returns the average of the arguments. The value of a text is 0.
        AVERAGEIF function
        AVERAGEIF returns the average (arithmetic mean) of all the cells in a range that meet the given criteria.
        AVERAGEIFS function
        AVERAGEIFS returns the average (arithmetic mean) of all cells that meet multiple criteria.
        BASE function
        BASE converts a positive integer to a specified base into a text from the numbering system. The digits 0-9 and the letters A-Z are used.
        BIN2DEC function
        BIN2DEC converts a binary number to a decimal number.
        BIN2HEX function
        BIN2HEX converts a binary number to a hexadecimal number.
        BIN2OCT function
        BIN2OCT converts a binary number to an octal number.
        CEILING function
        CEILING rounds a number up to the nearest multiple of significance.
        CHOOSE function
        CHOOSE uses an index to return a value from a list of up to 30 values.
        CHAR function
        CHAR finds and returns a character in Unicode character map by the number. The number is between 1 and 255.
        CODE function
        CODE returns a numeric code for the first character in a text string.
        COLUMN function
        COLUMN returns the column number of a cell reference.
        COLUMNS function
        COLUMNS returns the column number of a cell reference.
        COMBIN function
        COMBIN returns the number of combinations for a given number of items.
        CONCATENATE function
        CONCATENATE combines several text strings into one string.
        CONVERT function
        CONVERT converts a value from one unit of measurement to a different unit of measurement.
        COS function
        COS returns the cosine of the given angle.
        COSH function
        COSH returns the hyperbolic cosine of a number.
        COT function
        COT returns the cotangent of the given number.
        COTH function
        COTH returns the hyperbolic cotangent of the given number.
        COUNT function
        COUNT counts how many numbers are in the list of arguments. Text entries are ignored.
        COUNTA function
        COUNTA counts how many values are in the list of arguments. Text entries are also counted, even when they contain an empty string of length 0. If an argument is an array or reference, empty cells within the array or reference are ignored.
        COUNTBLANK function
        COUNTBLANK returns the number of empty cells.
        COUNTIF function
        COUNTIF returns the number of cells that meet criteria within a cell range.
        COUNTIFS function
        COUNTIFS applies criteria to cells across multiple ranges and counts the number of times all criteria are met.
        DATE function
        DATE converts a date written as year, month, day to an internal serial number and displays it in the formatting of the cell.
        DATEDIF function
        Returns the number of years, months, or days between two dates.
        DATEVALUE function
        DATEVALUE returns the internal date number for text in double quotation marks when the text format indicates a date. The internal number is returned as a natural number, resulting from the date system used by HCL Connections™ Docs to calculate dates.
        DAY function
        DAY returns the day of a given date value. The day is returned as a value 1 - 31. You can also enter a negative date or time value.
        DAYS function
        DAYS calculates the difference between two date values. The result is an integer and returns the number of days between the two days.
        DAYS360 function
        DAYS360 returns the difference between two dates based on the 360-day year that is used in interest calculations. The result is an integer.
        DEC2BIN function
        DEC2BIN converts a decimal number to a binary number.
        DEC2HEX function
        DEC2HEX converts a decimal number to a hexadecimal number.
        DEC2OCT function
        DEC2OCT converts a decimal number to an octal number.
        DEGREES function
        DEGREES converts radians into degrees.
        DOLLAR function
        DOLLAR converts a number to an amount in the currency format, rounded to a specified decimal place. You set the currency format in your system settings.
        ERFC function
        ERFC returns the complementary error function, integrated between a number and infinity.
        ERRORTYPE function
        ERRORTYPE returns the number corresponding to an error value occurring in a different cell. With the aid of this number, you can create an error message. If an error occurs, the function returns a logical or numerical value.
        EVEN function
        EVEN rounds a positive number up to the next even integer and a negative number down to the next even integer.
        EXP function
        EXP returns e raised to the power of a number. The constant e has a value of approximately 2.71828182845904.
        EXACT function
        EXACT compares two text strings and returns TRUE if they are identical. This function is case-sensitive.
        FACT function
        FACT returns the factorial of a number. Factorials are calculated as 1*2*3*4* ... * .
        FACTDOUBLE function
        FACTDOUBLE returns the double factorial of a number.
        FALSE function
        FALSE returns the logical value FALSE. The FALSE() function does not require any arguments, and always returns the logical value FALSE.
        FIND function
        FIND locates a text string within another text string and returns the starting character position of the first text string from the second text string. (case-sensitive).
        FIXED function
        FIXED specifies that a number be displayed with a fixed number of decimal places, with or without a thousands separator. Use this function to apply a uniform format to a column of numbers.
        FLOOR function
        FLOOR rounds a number down to the nearest multiple of significance.
        FORMULA function
        FORMULA displays the formula of a formula cell at any position. The formula is returned as a string in the reference position. If no formula cell can be found, or if the presented argument is not a reference, the error value #N/A is set.
        FREQUENCY function
        FREQUENCY indicates the frequency distribution in a set of values.
        GAMMALN function
        GAMMALN returns the natural logarithm of the gamma function.
        GCD function
        GCD returns the greatest common divisor of all arguments.
        HEX2BIN function
        HEX2BIN converts a hexadecimal number to a binary number.
        HEX2DEC function
        HEX2DEC converts a hexadecimal number to a decimal number.
        HEX2OCT function
        HEX2OCT converts a hexadecimal number to an octal number.
        HLOOKUP function
        HLOOKUP searches for a value and reference to the cells under the selected area. This function verifies if the first row of an array contains a certain value. The function returns the value in a row of the array, which is named in the index, in the same column.
        HOUR function
        HOUR returns the hour for a given time value. The hour is returned as an integer 0 - 23.
        HYPERLINK function
        HYPERLINK returns a link that points to a network resource or to a range referenced by the link. If you use the optional cell_text argument, the formula locates the URL, and then the cell_text value is displayed.
        IF function
        IF specifies a logical test to be performed.
        IFERROR function
        IFERROR returns the value that you specify if the expression is an error. Otherwise, returns the result of the expression.
        IFS function
        IFS runs logical tests to check whether one or more conditions are met and returns a value that matches the first TRUE condition.
        INDEX function
        INDEX returns the contents of a cell, specified by row and column number or an optional range name.
        INDIRECT function
        INDIRECT returns the reference specified by a text string. Use this function if you want a formula to always refer to a particular cell address, regardless of the contents of the cell after rows or cells have been moved or deleted. This function can also be used to return the area of a corresponding string.
        IFNA function
        IFNA returns the value that you specify if the expression returns the #N/A error value. Otherwise, returns the result of the expression.
        INT function
        Rounds a number down to the nearest integer. Negative numbers round down to the integer less than the number.
        ISBLANK function
        ISBLANK returns TRUE if the reference to a cell is blank. This function is used to determine if the content of a cell is empty. A cell with a formula inside is not empty. If an error occurs, the function returns a logical or numeric value.
        ISERR function
        ISERR returns TRUE if the value refers to any error value except #N/A. You can use this function to control error values in cells. If an error occurs, the function returns a logical or numeric value.
        ISERROR function
        ISERROR tests if the cells contain general error values. ISERROR recognizes the #N/A error value. If the cell being referenced contains any error such as #N/A, #DIV/0 or #Err 522, it returns TRUE.
        ISEVEN function
        ISEVEN returns TRUE if the value is an even integer, or FALSE if the value is an odd integer.
        ISFORMULA function
        ISFORMULA returns TRUE if a cell is a formula cell. If an error occurs, the function returns a logical or numeric value.
        ISLOGICAL function
        ISLOGICAL returns TRUE if the cell contains a logical number format. The function is used to check for both TRUE and FALSE values in cells. If an error occurs, the function returns a logical or numeric value.
        ISNA function
        ISNA returns TRUE if a cell contains the #N/A (value not available) error value. If an error occurs, the function returns a logical or numeric value.
        ISNONTEXT function
        ISNONTEXT tests if the cell contents are text or numbers, and returns FALSE if the contents are text. If an error occurs, the function returns a logical or numeric value.
        ISNUMBER function
        ISNUMBER returns TRUE if the value refers to a number. If an error occurs, the function returns a logical or numerical value.
        ISODD function
        ISODD returns TRUE if the value is an odd integer, or FALSE if the number is an even integer.
        ISREF function
        ISREF tests if the content of one or several cells is a reference. This function verifies the type of references in a cell or a range of cells. If an error occurs, the function returns a logical or numerical value.
        ISTEXT function
        ISTEXT returns TRUE if the cell contents refer to text. If an error occurs, the function returns a logical or numeric value.
        LARGE function
        LARGE returns the nth largest value from a set of values.
        LCM function
        LCM returns the lowest common multiple of all numbers in the list.
        LEFT function
        LEFT returns the first character or characters in a text string.
        LEN function
        LEN returns the length of a string including spaces.
        LENB function
        LENB returns the number of bytes used to represent the characters in a text string.
        LN function
        LN returns the natural logarithm based on the constant e of a number. The constant e has a value of approximately 2.71828182845904.
        LOG function
        LOG returns the logarithm of a number to the specified base.
        LOG10 function
        LOG10 returns the base-10 logarithm of a number.
        LOOKUP function
        LOOKUP returns the contents of a cell either from a one-row or one-column range or from an array.
        LOWER function
        LOWER converts all uppercase letters in a text string to lowercase.
        MATCH function
        MATCH returns the relative position of an item in an array that matches a specified value. The function returns the position of the value found in the lookup_array as a number.
        MAX function
        MAX returns the maximum value in a list of arguments.
        MEDIAN function
        MEDIAN returns the median (middle) value in the list.
        MID function
        Returns the specified portion of a string expression
        MIN function
        MIN returns the minimum value in a list of arguments.
        MINUTE function
        MINUTE calculates the minute for an internal time value. The minute is returned as a number 0 - 59.
        MMULT function
        MMULT calculates the array product of two arrays.
        MOD function
        MOD returns the remainder when one integer is divided by another.
        MODE function
        MODE returns the most common value in a data set. If there are several values with the same frequency, it returns the smallest value. An error occurs when a value does not appear twice.
        MONTH function
        MONTH returns the month for the given date value. The month is returned as a number 1 - 12.
        MROUND function
        MROUND returns a number rounded to a specified multiple.
        MULTINOMIAL function
        MULTINOMIAL returns the multinomial coefficient of a set of numbers.
        N function
        N returns the number 1 if the parameter is TRUE. N returns the parameter if the parameter is a number. N returns the number 0 for other parameters. If an error occurs, the function returns a logical or numerical value.
        NA function
        NA returns the error value #N/A.
        NETWORKDAYS function
        NETWORKDAYS returns the number of workdays between the start date and the end date. Optionally, a list of holidays can be deducted.
        NOT function
        NOT inverts and returns the logical value.
        NOW function
        NOW returns the computer system date and time. The value is updated when you recalculate the document or each time that a cell value is modified.
        OCT2BIN function
        OCT2BIN converts an octal number to a binary number.
        OCT2DEC function
        OCT2DEC converts an octal number to a decimal number.
        OCT2HEX function
        OCT2HEX converts an octal number to a binary number.
        ODD function
        ODD rounds a positive number up to the nearest odd integer and a negative number down to the nearest odd integer.
        OFFSET function
        OFFSET returns the value of a cell the location of which is offset by a certain number of rows and columns.
        OR function
        OR returns TRUE if at least one argument is TRUE. This function returns the value FALSE only when all the arguments have the logical value FALSE.
        PI function
        PI returns 3.14159265358979, the value of the mathematical constant PI to 14 decimal places.
        POWER® function
        POWER® returns a number raised to a power.
        PRODUCT function
        PRODUCT multiplies all the numbers given as arguments and returns the product.
        PROPER function
        PROPER capitalizes the first letter in all words of a text string.
        RADIANS function
        RADIANS converts degrees to radians.
        RAND function
        RAND returns a random number between 0 and 1.
        RANDBETWEEN function
        RANDBETWEEN returns a random integer number in a specified range.
        RANK function
        RANK returns the rank of a number in a sample.
        REPLACE function
        REPLACE replaces part of a text string with a different text string. This function can be used to replace both characters and numbers, which are automatically converted to text. The result of the function is always displayed as text.
        REPT function
        REPT repeats a character string by the given number of copies. The result can be a maximum of 255 characters.
        RIGHT function
        RIGHT defines the last character or characters in a text string.
        RIGHTB function
        In languages that use the double-byte character set, RIGHTB returns the specified number of bytes at the end of a text string.
        ROMAN function
        ROMAN Converts an arabic numeral to roman, as text.
        ROUND function
        ROUND rounds a number to a number of decimal places. This function rounds to the nearest number. See ROUNDDOWN and ROUNDUP for alternatives.
        ROUNDDOWN function
        ROUNDDOWN rounds a number down, toward zero, to a certain precision. See ROUNDUP and ROUND for alternatives.
        ROUNDUP function
        ROUNDUP rounds a number up, away from zero, to a certain precision. See ROUNDDOWN and ROUND for alternatives.
        ROW function
        ROW returns the row number of a cell reference.
        ROWS function
        ROWS returns the number of rows in a reference or array.
        SEARCH function
        SEARCH returns the position of a text segment within a character string. You can set the start of the search as an option. The search text can be a number or any sequence of characters. The search is not case-sensitive.
        SECOND function
        SECOND returns the second for the given time value. The second is returned as an integer 0 - 59.
        SERIESSUM function
        SERIESSUM returns the sum of a power series based on the formula.
        SHEET function
        SHEET returns the sheet number of a reference or a string representing a sheet name. If you do not enter any parameters, the result is the sheet number of the spreadsheet containing the formula.
        SIGN function
        SIGN returns the algebraic sign of a number. It returns 1 if the number is positive, -1 if the number is negative, and 0 if the number is zero.
        SIN function
        SIN returns the sine of the given angle.
        SINH function
        SINH returns the hyperbolic sine of a number.
        SMALL function
        SMALL returns the nth smallest value from a set of values.
        SQRT function
        SQRT returns the positive square root of a number.
        STDEV function
        STDEV estimates the standard deviation based on a sample.
        STDEVP function
        STDEVP calculates the standard deviation based on the entire population.
        SUBSTITUTE function
        SUBSTITUTE replaces old text with new text in a string.
        SUBTOTAL function
        SUBTOTAL calculates subtotals. If a range already contains subtotals, these are not used for further calculations.
        SUM function
        SUM adds all the numbers in a range of cells.
        SUMIFS function
        SUMIFS totals the arguments that meet multiple conditions.
        SUMIF function
        SUMIF adds the cells specified by the given criteria. This function is used to browse a range when you search for a certain value.
        SUMPRODUCT function
        SUMPRODUCT multiplies corresponding elements in the given arrays, and returns the sum of those products.
        T function
        T converts a number to a blank text string.
        TEXT function
        TEXT converts a number into text according to a given format.
        TIME function
        TIME returns the current time value from values for hours, minutes, and seconds. This function can be used to convert a time based on these three elements to a decimal time value.
        TIMEVALUE function
        TIMEVALUE returns the internal time number from the text enclosed by double quotation marks, indicating a possible time entry format. The internal number indicated as a decimal is the result of the date system used in HCL Connections™ Docs to calculate date entries.
        TODAY function
        TODAY returns the current computer system date. The value is updated when you reopen the document or modify the values of the document.
        TRIM function
        TRIM removes spaces that are in front of a string, or aligns cell contents to the left.
        TRUE function
        TRUE returns the logical value TRUE. The TRUE() function does not require any arguments, and always returns the logical value TRUE.
        TRUNC function
        TRUNC truncates a number by removing decimal places.
        TYPE function
        TYPE returns the type of value. If an error occurs, the function returns a logical or numeric value.
        UPPER function
        UPPER converts the string specified in the text field to uppercase.
        VALUE function
        VALUE converts a text string into a number.
        VLOOKUP function
        VLOOKUP uses a vertical search with reference to adjacent cells.
        VAR function
        VAR estimates the variance based on a sample.
        VARA function
        VARA estimates the variance based on a sample. In addition to numbers, text and logical values such as TRUE and FALSE are included.
        VARP function
        VARP calculates the variance based on the entire population.
        VARPA function
        VARPA calculates the variance based on the entire population. In addition to numbers, text and logical values such as TRUE and FALSE are included.
        WEEKDAY function
        WEEKDAY returns the day of the week for the given date value. The day is returned as an integer between 1 and 7. The day of the week on which numbering begins depends on the type.
        WEEKNUM function
        WEEKNUM calculates the week number of the year for the internal date value.
        WORKDAY function
        WORKDAY returns a date number that can be formatted as a date. You then see the date of a day that is a certain number of workdays away from the start date.
        YEAR function
        YEAR returns the year as a number according to the internal calculation rules.
    - Spreadsheets quick reference
      This topic helps you discover or remember how to do certain spreadsheet tasks.
    - Spreadsheets keyboard shortcuts
      You can use shortcut keys to perform tasks in spreadsheets. All shortcuts are valid on Windows™ operating systems. On Mac OS, press Command instead of Ctrl.
  - Working with presentations
    Create high impact, professional presentations. Or, you can upload existing presentations from your computer to HCL Docs Cloud for online editing. You can edit presentations with your colleagues in real-time and collaborate with others through comments and discussions.
  - Glossary
  - Notices

SUM function

SUM adds all the numbers in a range of cells.

Syntax

SUM(number1, number2, ...number30)

number 1 to number 30 are up to 30 arguments whose sum is to be calculated.

Example

If you enter the numbers 2, 3, and 4 in the Number 1, 2, and 3 text boxes, 9 is returned as the result.

=SUM(A1,A3,B5) calculates the sum of the three cells. =SUM (A1:E10) calculates the sum of all cells in the A1 to E10 range.

Conditions linked by AND can be used with the function SUM() in the following manner:

Example assumption: You have entered invoices into a table. Column A contains the date value of the invoice, column B contains the amounts. You want to find a formula that you can use to return the total of all amounts for a specific month only. For example, you want to return only the amount for the period >=2008-01-01 to <2008-02-01. The range with the date values covers cells A1:A40, the range containing the amounts to be totaled is in cells B1:B40. C1 contains the start date, 2008 -01-01, of the invoices to be included, and C2 the date, 2008 -02-01, that is no longer included.

Enter the following formula as an array formula:

=SUM((A1:A40>=C1)*(A1:A40<C2)*B1:B40)

To enter this formula as an array formula, you must press the Shift+ Ctrl+Enter keys instead of pressing the Enter key to close the formula. The formula is then shown in the formula bar enclosed in braces.

{=SUM((A1:A40>=C1)*(A1:A40<C2)*B1:B40)}

The formula is based on comparison results of 1 if the criterion is met and 0 if it is not met. The individual comparison results are treated as an array and used in matrix multiplication, and then the individual values are totaled to give the result matrix.

Rate this topic

5 stars 4 stars 3 stars 2 stars 1 star

Comment on this topic.

By clicking this box, you acknowledge that you are NOT a U.S. Federal Government employee or agency, nor are you submitting information with respect to or on behalf of one. HCL provides software and services to U.S. Federal Government customers through its partners immixGroup, Inc. Contact this team at https://hcltechsw.com/resources/us-government-contact. Do not include any personal data in this Comment box. Note: To report a problem or question about the product software, do not use this form. Instead, go to the HCL Software Support site.