When entering data to a set of rows or columns, there is the possibility that one of these values is wrong, so it is necessary to omit errors when making a calculation within that range.
Whether you need to query the location and data of that range of cells to be summed, or simply select the set, such errors can be part of the formula.
Everything will depend directly on the needs of each user, where you will define if you want to specify the calculation taking into account or not the number of errors that the range presents.
Index( )
- Learning to use ” ADD IF ” to sum a range of cells
- Is there a formula that allows you to skip errors when adding a range?
Learning to use ” ADD IF ” to sum a range of cells
Also known as ” COUNT IF “, it is made up of a certain number of cells whose numerical values can be added, and its result displayed in a new section within the spreadsheet.
The context of said formula is based on a criterion, which must meet the group of values that is selected; so that these are finally added in an integer or decimal value, depending on the characteristics of the number that integrates the operation.
Formula syntax
Before talking about the sum of a data range where errors should be ignored; It is necessary to know the way in which the formula to be used is conjugated, within the spreadsheet available in the program.
As explained previously, ADD IF is responsible for adding a group of cells that is part of the sheet, and depending on this number the style of formula that must be transcribed will vary .
A quick example is to place “A2 + A3” in case of two cells whose numbers must be added; but if instead the range is significantly expanded, the position where it begins and ends must be entered into the formula.
In this way, each user should transcribe the following within the formula bar or within the same cell of the sheet: “ = sum (A2: A8) ”.
If it is necessary to make the calculation to a different group of ranges, you should know that you can integrate their cells within the parentheses; separate them from each other by a comma , and then press “Enter” to automatically receive the result.
Is there a formula that allows you to skip errors when adding a range?
Fortunately the answer is yes. Among the characters capable of being part of a calculation, those that contain letters or symbols of any type are excluded.
Therefore, if a range that you have written has a value that meets these criteria, you should know that they cannot be taken into account within ADD IF; and consequently it will announce an error.
As mentioned above, there is a way to make these errors part of the range, and whether or not they are taken into account, you can find values and give a result expressed in text or numbers.
Syntax for omitting errors within a range when adding
Like other aspects within Microsoft Excel, the ” IFERROR ” function works as an adjective within the formula syntax. In this way, it tells the calculation system that every time it finds a value other than a numeric character; take it as null and continue adding with the rest of the numbers.
In this way, when transcribing the formula into the cell, it will be constituted as follows: ” = SUM (IF.ERROR (Range of cells,” “)) “, which can also be applied within the “AVERAGE” function to calculate said range.
It should be noted that since the ranges are generally wide; This function must be used as an array, therefore the key combination ” Control + Shift + Enter ” must be used .
Through this simple method, errors can be omitted within a table of values or a certain range of data, even managing to replace formulas and functions in favor of your needs.
Thanks to such formulas, multiple users will be able to save hours of data analysis in search of an error, in order that SUMAR SI can work them correctly.
