Sometimes, you need to know how many values a specific range contains in Excel . For this, it is possible to use formulas and functions in order to obtain a result. If you are interested in knowing how to count numbers other than zero in Excel, pay attention to this valuable tutorial.
Index( )
- What function can you use to count your values in Excel?
- SI
- ADDITION
- How can you count unique values among duplicates with Excel?
- SUM PRODUCT
- How to remove duplicate data from your table in Excel with the unique rows?
What function can you use to count your values in Excel?
There are two functions you can use to count your values in Excel. One of them is the COUNTIF function through which you can get a result under a condition. On the other hand, the ‘SUM’ function will allow you to get the sum of a set of values stored in several cells.
CONTAR.SI
The COUNTIF function allows you to count the number of cells in a data range as long as they meet a condition or criteria. Only when a condition is fulfilled, the value is counted. The COUNTIF function requires two parameters. One is the range to be counted . In addition, the criteria or condition that indicates which cells will be counted is needed.
It is important to note that it does not matter if the elements of the range are numeric values or characters . For example, you can find out how many items are green in an item list. It is enough to specify in the function the range of articles where the color was saved and indicate the ‘green’ criterion. So the formula would be =COUNTIF(B2:B11, ‘green’)
It is possible to count non-zero numbers with the COUNTIF function. Suppose we have a range of data related to the number of children of a group of employees. If we want to add up how many of them have children, the function would be =COUNTIF(B2:B11, “< > 0”). This will display the result of the count if the condition is met.
ADDITION
For its part, the SUM function allows you to add a series of values, ranges of a cell or references from Microsoft Excel. Unlike COUNTIF, the SUM function does require numeric values in order to produce a result. SUM requires only one parameter or argument , either a range or a list of values separated by a comma.
So, you can add three cells with salary values, to cite an example. To do this, you’ll need to type in the formula =SUM(A2:A11) where A2:A11 is the range of numeric values . Also, you can do the addition by inserting elements separately. The function would be =SUM(2500, 3400, 1200). In this case, you can add up to 255 numbers.
How can you count unique values among duplicates with Excel?
You may need to count unique values among duplicates in Excel. To do this, you can use a filter or combine functions. If you decide to use a filter, select the cell range including its header. Then, look for the ‘Sort and filter’ section and under ‘Data’ choose ‘Advanced’.
Then you will see a dialog box to filter data in Excel . Click ‘Copy to another location’. Then, check the ‘Only unique records’ box and press ‘OK’. Under the last row of the range add the function ROWS and the range, like this: =ROWS(A2:A11). Replace A2:A11 with your cell range.
Also, you can count unique values by combining functions . Suppose we want to count the unique values from the range C2:C21. To do this, type =SUM(IF(FREQUENCY(IF(LEN(C2.C21))) > 0, MATCH(C2.C21, C2:C21, 0), “”), IF(LEN(C2:C21)) > 0 , MATCH(C2:C21, C2:C21, 0)) > 0,1)) to not include blank cells.
SUM PRODUCT
The SUMPRODUCT function allows obtaining the sum of the products of a set of values that has a respective range or matrix . This function requires as a mandatory parameter the use of an array or range that includes only numbers. You can add up to 255 components to the formula.
SUMPRODUCT can use other operators to separate parameters besides the comma. These operators include +, -*, and / to add, subtract, multiply, or divide in Excel . After performing the indicated operation, the results are added. The ranges must have the same size so that there is no error in the formula.
How to remove duplicate data from your table in Excel with the unique rows?
It is possible to remove duplicate data from an Excel table with unique rows. To achieve this, you will need to compare the columns you select and remove any rows that contain duplicate values. First, select the table including its headers. Go to ‘Data’ and under ‘Data Tools’ click ‘Remove Duplicates’.
Next, select the columns where you want to apply the changes and check the ‘My data has headers’ box. Then click the ‘OK’ button. Excel will show you a window that will indicate the number of duplicate and removed values , as well as the number of values that remain in the table.
