## How to Sum/Calculate Total Values with Duplicate Values Only Once in Excel

In statistic when calculating the total values for a range of numbers, we often count duplicate values only once. Normally, if only apply SUM function to calculate total values, it will cover all values including all duplicate values. So, we need to update the formula to make it meet our requirement. This free tutorial will show you the formula to sum values with duplicate values only once in the selected range.

Precondition:

See screenshot below. We want to sum all values but for the duplicate values, we only count once.

## 1. Calculate Total Values with Counting Duplicate Values Only Once using Formula

Step1: In B7, enter the formula:

``=SUMPRODUCT(A2:B6/COUNTIF(A2:B6,A2:B6))``

In this formula, A2:B6 is the range selected for calculating.

Step2: Click Enter to get result. Verify that the sum value is calculated properly. Duplicate values like 100, 60 are only count once.

## 2. Calculate Total Values with Counting Duplicate Values Only Once using User Defined Function

You can create a User-Defined Function (UDF) in VBA to calculate the total values of a range while counting duplicate values only once. Here are the steps of how to do it:

Step1: press ALT + F11 to open the VBA editor in your workbook.

Step2: In the editor, go to Insert > Module to create a new module.

Step3: In the module, paste the following code. Save the module and go back to your Excel workbook.

```Function SumUnique_ExcelHow(ByVal rng As Range) As Double
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

Dim cell As Range
For Each cell In rng
If Not dict.exists(cell.Value) Then
End If
Next cell

Dim key As Variant
For Each key In dict.keys
SumUnique_ExcelHow = SumUnique_ExcelHow + key
Next key
End Function```

Step4: In any blank cell, enter the formula:

``=SumUnique_ExcelHow(A2:B6)``

Where A2:B6 is the range of values you want to calculate the total for.

## 3. Video: Calculate Total Values with Counting Duplicate Values Only Once

This video will show you how to calculate total values with counting duplicate values only once using either the SUMPRODUCT function or a user-defined function with VBA code.

## 4. Related Functions

• Excel SUMPRODUCT function
The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
• Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…

## How to Concatenate Text based on unique values in Another Column in Excel

This post will guide you how to concatenate text values based on unique values in another column in Excel. How do I concatenate cells based on specific criteria in Excel.

## 1. Concatenate Text Based on unique Values in Another Column

Assuming that you have a list of data in range A1:B6, in which contain product IDs and product Names. And you want to concatenate product names based on unique ID values (There are duplicated ID values in Column A), How to do it. You need to extract unique product IDs in another range, and then concatenating text values based on newly created range with a User Defined Function. Here are the steps:

Step1: you can use an Excel Array formula based on the IFERROR function, the INDEX function, the MATCH function and the COUNTIF function to extract the unique product ID values.

``=IFERROR(INDEX(\$A\$2:\$A\$6, MATCH(0,COUNTIF(\$C\$1:C1, \$A\$2:\$A\$6), 0)),"")``

Type this formula into cell C2, and press Ctrl + Shift + Enter keys on your keyboard to change it as array formula.  And then drag the AutoFill Handle down to other cells until getting blank cells.

Step2: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Step3: then the “Visual Basic Editor” window will appear.

Step4: click “Insert” ->”Module” to create a new module.

Step5: paste the below VBA code (code from here) into the code window. Then clicking “Save” button.

```Function Combinerows(CriteriaRng As Range, Criteria As Variant, _
ConcatenateRng As Range, Optional Delimeter As String = " , ") As Variant
Dim i As Long
Dim strResult As String
On Error GoTo ErrHandler
If CriteriaRng.Count <> ConcatenateRng.Count Then
Combinerows = CVErr(xlErrRef)
Exit Function
End If

For i = 1 To CriteriaRng.Count
If CriteriaRng.Cells(i).Value = Criteria Then
strResult = strResult & Delimeter & ConcatenateRng.Cells(i).Value
End If
Next i

If strResult <> "" Then
strResult = Mid(strResult, Len(Delimeter) + 1)
End If

Combinerows = strResult
Exit Function
ErrHandler:
Combinerows = CVErr(xlErrValue)
End Function```

Step6: back to the current worksheet, then type the following formula in a blank cell, and then press Enter key.

``=combinerows(A2:A6,C2,B2:B6)``

Step7: drag the AutoFill handle over other cells to  concatenate text based on unique product ID values.

## 2. Video: Concatenate Text based on unique values in Another Column

This video will show how to use a formula in combination with a User defined function with VBA code to concatenate text based on unique values in another column in Excel.

## 3. Related Functions

• Excel IFERROR function
The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)…
• Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
• Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
• Excel MATCH function
The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….

## How to Count Numbers with Leading Zeros

This post will guide you on how to count numbers with leading zeros in Excel 2013/2016/2019/365. When working with numbers that have leading zeros, it can be challenging to count the number of zeros that appear before the actual value. Fortunately, there are two main methods for counting leading zeros in Excel: using a formula or using VBA code.

In this post, we’ll walk through the steps for each method, so you can choose the one that works best for your needs.

## 1. Count Numbers with Leading Zeros using Formulas

Suppost you have a list of product ID in your worksheet, and the product ID contains the different number of leading zeros in the front of string. And if you use the COUNTIF function to count numbers with leading zeros, but it will remove those leading zeros, it means that 1289 and 001289 are the same strings.

So how to preserve the leading zeros while counting numbers. And you can use another Excel function named as SUMPRODUCT.

For example, you want to count strings with leading zeros in the range A2:A6, you can write down the following formula based on the SUMPRODUCT function.

``=SUMPRODUCT(--(\$A\$2:\$A\$6=A2))``

You can enter this formula into Cell B2, then press Enter key. Then you can drag AutoFill Handle down to other cells to apply this formula.

You can also use another excel array formula to count strings with leading zeros as follows:

``=SUM(IF(\$A\$2:\$A\$10=A2,1,0))``

## 2. Count Numbers with Leading Zeros using User Defined Function with VBA Code

Step1: press ALT+F11 to open the VBA editor.

Step2: Click on Insert -> Module to create a new module.

Step3: Copy and paste the following code into the module:

```Function CountLeadingZeros_ExcelHow(cell As Range) As Long
Dim count As Long
Dim value As String
value = cell.value
count = 0
While Left(value, 1) = "0" And Len(value) > 1
count = count + 1
value = Mid(value, 2)
Wend
If Left(value, 1) = "0" Then count = count + 1
End Function```

The VBA code uses a While loop to count the number of leading zero characters in the specified cell. And it checks whether the leftmost character of the cell’s value is “0” and whether the length of the cell’s value is greater than 1.

If both conditions are true, it increments a count variable and removes the leftmost character from the cell’s value using the Mid function.

Step4: Save the module and go back to your Excel worksheet.

Step5: Type the following formula in a blank cell to count the number of leading zero characters in cell A2.

``=CountLeadingZeros_ExcelHow(A2)``

Step6: Press Enter to see the result.

## 3. Video: Count Numbers with Leading Zeros

This video explains how to count numbers with leading zeros in Excel using a formula or VBA code. And it provides step-by-step instructions for both methods and demonstrates how to use them in practice.

## 4. Related Functions

• Excel SUMPRODUCT function
The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
• Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
• Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
• Excel SUM function
The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…

## 5. Related Posts

• How to insert leading zeros to number or text
Assuming that you want to insert leading zeros  to number in Cell A2, then you can try to use the TEXT function to create an excel formula. You can also use the CONCATENATE function to add the specific digit of leading zeros into each number…
• How to keep or remove leading zeros
If you want to keep the leading zeros as you type in a cell, you need to change the cell format to Text before you type the zip code number.You can remove or delete leading zeros in front of the cells with excel VBA macro…
• Count the number of words in a cell
If you want to count the number of words in a single cell, you can create an excel formula based on the IF function, the LEN function, the TRIM function and the SUBSTITUTE function. ..

## How to Count or Sum Cells with Checked Box in Excel

This post will guide you how to count the number of checked checkboxes in your worksheet in Excel. How do I Count or sum all cells with checked boxes with a formula in Excel.

Assuming that you have a list of data in range A1:C5, in which contain check boxes, product names and sale numbers. And you wish to count the number of all checked boxed in each row in your range. Or you want to sum all sales with checked checkboxes in your range, how to accomplish it. This post will show you one method to sum or count checked checkboxes with the following steps.

## 1. Count or Sum Cells with Checked Box

Step1: you need to select each checked box one by one, and then choose Format Control from the context menu. And the Format Control dialog will open.

Step2: click the Control tab in the Format Control dialog box, and type the cell address in the Cell link text box to link the control to a cell places the value True if the box is checked in the linked cell.

Step3: you need to repeat the above two steps to set a linked cell for other checked boxes.

Step4: then you can insert a formula based on the COUNTIF function in the blank cell that you want to place the last result, such as: Cell E1. Type the following formula into the Cell E1 to Count the number of cells with Checked box in your range A1:C5:

``=COUNTIF(D2:D5,TRUE)``

Then press Enter key on your keyboard to apply this formula. You would see that the number of Cells with checked checkboxes is calculated.

Step5: if you want to sum all sales values with checked checkbox in your range, you can use another formula based on the SUMPRODUCT function to accomplish it. Like this:

``=SUMPRODUCT((\$D\$2:\$D\$5=TRUE)*\$C\$2:\$C\$5)``

Then you need to type this formula in the desired cell, press Enter key on your keyboard to apply it. You would see that the sum of sales value with checked checkboxes is calculated.

## 2. Video: Count or Sum Cells with Checked Box in Excel

In this video, you will learn how to use the COUNTIF and SUMPRODUCT formulas to count or sum cells with checked boxes in Excel.

## 3. Related Functions

• Excel SUMPRODUCT function
The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
• Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…

## Find Missing Numbers in a Sequence in Excel

If you’re working with data in Excel and notice that there are some missing numbers in a sequence, it can be frustrating to try and figure out where they are. Fortunately, there are a few ways to find these missing numbers quickly and easily.

This post will guide you how to find missing numbers in a sequence with a formula in Excel. How do I identify missing numbers in a consecutive series in Excel. How to find missing serial number in Excel 2013/2016.

This post will guide you through two methods to find missing numbers in a sequence in Excel: using a formula and using VBA code.

## 1. Find Missing Numbers in a Sequence in Excel

Assuming that you have a serial number list in Column B, and you want to find the missing number in this sequence list. How to achieve it.

You can use an excel array formula based on the SMALL function, the IF function, the ISNA function, the MATCH function, and the ROW function. Like this:

``=SMALL(IF(ISNA(MATCH(ROW(B\$1:B\$20),B\$1:B\$20,0)),ROW(B\$1:B\$20)),ROW(B1))``

Type this array formula into a blank cell, and then press Ctrl + Shift + Enter keys in your keyboard.

The missing numbers are listed in cells.

Or you can use another array formula based on the Small function, the IF function, the Countif function and the row function to achieve the same result. Like this:

``=SMALL(IF(COUNTIF(\$B\$1:\$B\$10,ROW(\$1:\$20))=0,ROW(\$1:\$20),""),ROW(B1))``

## 2. Find Missing Numbers in a Sequence using VBA Code in Excel

To find missing numbers in a sequence in Excel with VBA code, you can follow these steps:

Step1: Press Alt + F11 to open the Visual Basic Editor (VBE) or click Developer > Visual Basic.

Step2: In the VBE window, right-click your workbook and click Insert > Module.

Step3: In the module window, paste the following code:

```Sub FindMissingNumbers_ExcelHow()
Dim rng As Range
Dim outRng As Range
Dim i As Long
Dim n As Long
Dim found As Range

' Prompt the user to select the range of data to export
Set rng = Application.InputBox(prompt:="Select the range that contain a sequence list", Type:=8)
Set outRng = Application.InputBox(prompt:="Select one cell as output range", Type:=8)

n = Application.Min(rng) 'get the minimum number in the input range

For i = 1 To Application.Max(rng) - n + 1 '
Set found = rng.Find(n, LookIn:=xlValues, LookAt:=xlWhole)
If found Is Nothing Then
outRng.Value = n
Set outRng = outRng.Offset(1)
End If
n = n + 1
Next i

End Sub```

Step4: Press ALT + F8 on your keyboard to open the Macro dialog box. Select the FindMissingNumbers_ExcelHow macro from the list and click the Run button.

Step5: Select one range of cells that contains a sequence to find missing numbers.

Step6: Select one cell as output range to place the missing numbers.

Step7: The missing numbers in the sequence will be listed in the output range.

## 3. Video: Find Missing Numbers in a Sequence in Excel

This video will demonstrate both a formula and VBA code to help you find missing numbers in a sequence in Excel.

## 4. Related Functions

• Excel MATCH function
The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
• Excel SMALL function
The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
• Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
• Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
• Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…

## Count Attendance and Absence with COUNTIF function

In our campus life and work life, we usually record everyone’s attendance. Today we will introduce you the application of Excel COUNTIF function to count the attendance.

Using a week as an example, we use the following example to show you how to use Excel COUNTIF function to count attendance.

As shown above, attendance and absence are counted correctly by Excel COUNTIF function.

## 1. FORMULA

In this example, we have used only the COUNTIF function, without nesting other functions. In a simple case like the one above, we can use COUNTIF function to solve our problem directly.

COUNTIF function belongs to Excel Statistical functions. It counts the number of cells that meet the provided criterion in a certain range.

Syntax:

`=COUNTIF(range, criteria)`

In this example, we can directly apply this function with entering our own range and criteria.

`=COUNTIF(B2:F2,"√") – in G2`

## 2. EXPLANATION

In this example, the “√” in the cell indicates attendance on the corresponding day. Calculating attendance is equivalent to calculating how many ticks there are in B2:F2.

### a. FULL ATTENDANCE

Use G2 as an example, the range for recording attendance is B2:F2. The condition is “√”. So the formula is =COUNTIF(B2:F2,”√”). We cannot enter “√” in this formula, but we can copy a √ into criteria field and include it in brackets.

In the formula bar, we can expand B2:F2, we can get an array of “√”

``=COUNTIF({"√","√","√","√","√"},"√")``

Excel COUNTIF function will count the number of times “√” appears in the array.

Obviously, the result is 5.

### b. PARTIAL ATTANDENCE

Copy down the formula. G3 for example, B3 and E3 cells in the absence of “√”, so in this formula, there are only three “√” in the range.

After running the formula, the result is 3.

### c. ABSENCE

If the cell is empty, it means it was absent that day. Just replace “√” with “”.

The formula is

``=COUNTIF(B2:F2,"")``

## 3. Related Functions

• Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…

## How to Sort a Column by Occurrence Count in Excel

This post will guide you how to sort a column by occurrence count in Excel. How do I sort data in one column by the most frequent value in Excel 2013/2016/2019/365.

## 1. Sort A Column by Occurrence Count

Assuming that you have a list of data in one column, and you want to sort this column descending by these occurrence count numbers. How to do it. And you need to count the occurrences of the values in that column, and then use the sort command to sort that column. You can use CountIF function to count the frequency of occurrence of your list in one column, and then you can sort this column descending by these count numbers. Just do the following steps:

Step1: select one newly column beside your original column as helper column. And then you need to type the following column in the helper column to count the occurrence number.

``=COUNTIF(\$B\$1:\$B\$7, B1)``

You need to press Enter key to apply this formula, and then drag the AutoFill handle down to other cells to apply this formula.

Step2: keep to select the helper column, and go to Data tab, click Sort A to Z command under Sort & Filter group. And the Sort Warning dialog will open.

Step3: select Expand the selection option in the Sort Warning dialog box, and click Sort button. The helper column should be sorted by count numbers, and your original columns should also be sorted by count numbers.

Step4: now you can remove helper column.

## 2. Video: Sort a Column by Occurrence Count in Excel

This video will demonstrate how to sort a column by occurrence count in Excel using COUNTIF formula.

## 3. Related Functions

• Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…

## How to Count Cells that do not Contain Specific Text in Excel

In the previous post, we talked that how to count cell that contain certain text using the COUNTIF function in Excel.

This post will guide you how to count the number of cells that do not contain specific text within a range of cells using a formula in Excel 2013/2016 or Excel 365. How do I count the number of cells without certain text using a simple formula in Excel. When you analyze a large list of data, you may also want to know that how many cells that do not contain specific text. The below steps will show you how to do it in a simple way.

## 1. Count Number of Cells that do Not Contain Specific Text Using COUNTIF Function

You can use the COUNTIF function to count cells that do not contain certain text. And you also need to supply the target text string in the criteria argument in the COUNTIF function.

The below is a generic formula to count the number of cells that do not contain specific text:

``=COUNTIF(range,criteria)``

Assuming that you have a list of data in range B1:B6 and you want to count the number of cells without a certain text “exc”. And you can type this string in the second argument as its criteria and you will get the below formula:

``=COUNTIF(B1:B6,"<>*exc*")``

LET’S SEE THAT HOW THIS FORMULA WORKS:

The COUNTIF function can be used to count the number of cells that match a single condition or criteria. And in this case, you need to provide a criteria as “<>*exc*”, which is evaluated as “values that do not contain ‘exc’ in any position”. Then the total count of all cells in the range B1:B6 that meet the above criteria is returned.

The “<>” operator means that does not equal to a certain value, so the above formula can be used to count any cell that does not contain “exc” in any position in cells.

## 2. Count Number of Cells that do Not Contain Specific Text With VBA Code

You can also use a User-defined function with VBA code to count number of cells that do not contain a specific text string in Excel, just do the following steps:

Step1: Press Alt + F11 to open the Visual Basic Editor.

Step2: In the Visual Basic Editor, click on Insert > Module.

Step3: In the new module, enter the following code:

```Function CountNotEqual_ExcelHow(rng As Range, criteria As String) As Long
CountNotEqual_ExcelHow = WorksheetFunction.CountIf(rng, "<>*" & criteria & "*")
End Function
```

Step4: Save the module with a suitable name, such as “CountNotEqual_ExcelHow“. And return to your worksheet, and enter a cell where you want to use the user-defined function.

Step5: In any blank cell, enter the formula:

``=CountNotEqual_ExcelHow(B1:B6,"exc")``

Where ” B1:B6” is the range you want to count and “exc” is the specific text you want to exclude.

Step6: Press Enter, and the formula will return the count of cells that do not contain the specific text.

## 3. Video: Count Number of Cells that do Not Contain Specific Text

This video will demonstrate two methods to count cells that do not contain specific text in Excel – using the COUNTIF function and VBA code.

## 4. Related Functions

• Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters. etc. = COUNTIF (range, criteria) …

## Count Non-Contiguous Range using COUNTIF

This post will guide you how to count a non-contiguous range with criteria in COUNTIF function in Excel 2013/2016 or Excel office 365. How do I build a formula for counting non-contiguous range in Excel using COUNTIF function or User Defined Function in Excel.

## 1. What is Non-Contiguous Range?

A non-contiguous range consists of tow of more separate ranges in your current worksheet. And this post will show you one formula that counting the non-contiguous range with criteria in Excel.

## 2. Generic Formula

If you want to count a non-contiguous range with criteria, and you can use the COUNTIF function in combination with the INDIRECT and the SUM function. The general formula is like this:

``=SUM(COUNTIF(INDIRECT({“range1″,”range2″,”range3”}),criteria))``

The syntax of Explanations is as below:

• SUM – the sum function can be used to add up values in the given cells.
• COUNTIF – COUNT the number of cells that match a certain condition.
• INDIRECT – return a valid cell reference from a given text string.

## 3. COUNT Non-Contiguous Range Using COUNTIF

Assuming you have tree ranges A1:A5, C1:C5, and E1:E5 in your worksheet and you wish to count the number of values that is greater than 80. And you can use the below formula:

``=SUM(COUNTIF(INDIRECT({"A1:A5","C1:C5","E1:E5"}),">80"))``

Let’s See That How This Formula Works:

The COUNTIF function can only count the number of cells in a range that match a given condition in Excel. And if you want to use the COUNTIF function with non-contiguous ranges, and you will get an error value. The best method is that you can use the INDIRECT function to get a valid cell reference from an array list that contain multiple text string.

The multiple ranges that returned by the INDIRECT function are passed into COUNTIF function. As the COUNTIF function receives an array list, it will return an array result also.

Finally, you can use the SUM function to add up all items in an array.

You can also use another solution to count Non-contiguous range using COUNTIF function. And using more that one COUNTIF function to count the numbers. The formula is like this:

``=COUNTIF(A1:A5,">80")+COUNTIF(C1:C5,">80")+COUNTIF(E1:E5,">80")``

You can also use the SUMPRODUCT function along with the COUNTIF function to count non-contiguous ranges in Excel. Just use the following formula:

``=SUMPRODUCT(COUNTIF(INDIRECT({"A1:A5","C1:C5","E1:E5"}),">80"))``

This formula will count the number of cells that meet your criteria in the non-contiguous ranges that you specify.

## 4. Count Non-Contiguous Range with VBA Code

You can count non-contiguous ranges in Excel with VBA code (user-defined function), just do the following steps:

Step1: Press the Alt and F11 keys at the same time to open the VBA editor.

Step2: In the VBA editor, select “Insert” from the menu bar and choose “Module” to create a new module.

Step3: Copy and paste the VBA code for the user-defined function into the module. Save the workbook with the VBA code.

```Function CountValuesAboveCriteria_excelHow(rngArray As Variant, criteria As Variant) As Long
Dim countArray() As Long
ReDim countArray(LBound(rngArray) To UBound(rngArray))

Dim i As Long
For i = LBound(rngArray) To UBound(rngArray)
countArray(i) = Application.CountIf(Range(rngArray(i)), criteria)
Next i

CountValuesAboveCriteria_excelHow = Application.Sum(countArray)
End Function```

Step4: Close the VBA editor and return to the worksheet where you want to use the function.

Step5: In any blank cell, enter the formula:

``= CountValuesAboveCriteria_excelHow(rngArray, criteria) ``

Where “rngArray” is an VBA array of range strings (e.g. {“A1:A5″,”C1:C5″,”E1:E5”}) and “criteria” is the criteria string (e.g. “>80“).

``=CountValuesAboveCriteria_excelHow({"A1:A5","C1:C5","E1:E5"},">80")``

Step6: Press Enter to see the result.

## 5. Video: Count Non-Contiguous Range in excel

This video will demonstrate both formula and VBA code methods for counting non-contiguous ranges in Excel.

## 6. Related Functions

• Excel SUM function
The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
• Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
• Excel INDIRECT function
The Excel INDIRECT function returns the cell reference based on a text string, such as: type the text string “A2” in B1 cell, it just a text string, so you can use INDIRECT function to convert text string as cell reference….

## How to Replace Duplicates with Blank Cells in Excel

This post will guide you how to remove duplicates and replace with blank cells in Excel 2013/2016. How do I replace the duplicates with blank cells using a formula in Excel.

## 1. Replace Duplicates with Blank Cells Using Formula

Assuming that you have a list of data in range B1:C4, and you want to remove duplicate values in this range of cells and replace it with blank cells. How to achieve it.  And if you just use the Remove Duplicates command, it will remove all duplicate rows only. So you can use a formula to remove duplicates and replace it with blank cell based on the IF function, and the COUNTIF function.

Just like this:

``=IF(B1="","",IF(COUNTIF(\$B1:B4,B1)=1,B1,""))``

Type this formula into a blank cell, such as: D1 and press Enter key in your keyboard, and then drag the AutoFill Handle down to Cell F4 to apply this formula.

## 2. Replace Duplicates with Blank Cells using Remove Duplicates

You can replace duplicates with blank cells in Excel by using Remove Duplicates feature. Just do the following steps:

Step1: Select the range of cells containing duplicates that you want to replace with blank cells.

Step2: Click on the “Data” tab in the Excel ribbon. Click on the “Remove Duplicates” button in the “Data Tools” group.

Step3: In the “Remove Duplicates” dialog box, make sure that all columns are selected.

Step4: Click “OK” to remove the duplicates.

## 3. Replace Duplicates with Blank Cells with VBA Code

You can also use the VBA code to replace duplicate with blank cells in Excel. it can use the Application.InputBox function to select a range and a destination cell. Just do the following steps:

Step1: Press Alt + F11 to open the Visual Basic Editor.

Step2: In the Visual Basic Editor, select Insert > Module to create a new module.

Step3: Copy and paste the VBA code provided into the new module. Save the workbook as a macro-enabled workbook with a .xlsm extension.

```Sub ReplaceDuplicatesWithOne_excelhow()
Dim sourceRange As Range
Dim destinationCell As Range
Dim cell As Range
Dim values As Variant
Dim i As Long, j As Long
Dim dict As Object

Set dict = CreateObject("Scripting.Dictionary")

'Select the range to search for duplicates
Set sourceRange = Application.InputBox(prompt:="Select range to search for duplicates", Type:=8)

'Select the destination cell to place the result
Set destinationCell = Application.InputBox(prompt:="Select destination cell for the result", Type:=8)

'Copy the values of the selected range to an array
values = sourceRange.Value

'Loop through the array and add unique values to a dictionary, replacing duplicates with blank cells
For i = 1 To UBound(values, 1)
For j = 1 To UBound(values, 2)
If dict.Exists(values(i, j)) Then
values(i, j) = ""
Else
End If
Next j
Next i

'Paste the result to the destination cell
destinationCell.Resize(UBound(values, 1), UBound(values, 2)).Value = values

End Sub```

Step4: Press Alt + F8 to open the Macro dialog box. Select the macro you want to run from the list of macros and click the Run button.

Step5: select the range to search for duplicates.

Step6: select one destination cell to place the result.

Step7: The VBA code will then execute and replace duplicates with blank cells or keep one copy of each duplicate value. The result will be placed in the selected destination cell.

## 4. Show Only Duplicate Values

If you want to replace all unique values with blank cells and only show duplicates values in Excel, you can use another formula based on the IF function and COUNTIF function. Like this:

``=IF(B1="","",IF(COUNTIF(\$B1:B4,B1)>1,B1,""))``

Type this formula into a blank cell and then drag the AutoFill Handle down to other cells to apply this formula.

Let’s see the result:

## 5. Show Only Unique Values

If you want only to show the unique values and remove all duplicate values and replace with blank cells in Excel, you can use the following formula based on the IF and COUNTIF function.

`=IF(B1="", "", IF(COUNTIF(B\$1:B\$4,B1)>1,"",B1))`

## 6. Video: Replace Duplicates with Blank Cells

This video will guide you through the steps on how to replace duplicates with blank cells in Excel using built-in Excel functions or VBA code.

## 7. Related Functions

• Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])…
• Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…

## How to Exclude Values from One Column Based on Values in Another Column in Excel

This post will explain various methods to exclude values from one column based on values in another column in Excel.

Often, while working with large datasets, we need to remove certain values from a column based on criteria from another column. we will cover three different approaches to achieve this: using the VLOOKUP function, the COUNTIF function, and VBA code.

Prepare a table recording the PASS and FAIL status for students. Now we need to exclude students who are listed in PASS column but also listed in FAIL.

## 1. Exclude Values from One Column by VLOOKUP Function

Step1: Insert a new column between column B and column C. Then in C2 enter the formula

``=VLOOKUP(B2,\$D\$2:\$D\$6,1,FALSE)``

Step2: Click Enter. Verify that Ada is displayed in this cell.

Step3: Drag down the fill handle. Verify that C2:C11 are filled with returned values of VLOOKUP function.

Through the screenshot we can see that values need to be removed are displayed in column C.

Step4: Create a filter on C1. Select row 1, click Data in ribbon, select Filter.

Step5: Click arrow button in C1, then Sort & Filter dialog is displayed. Check on Select All option first (this operation will activate all options checked), then uncheck #N/A. Click OK.

Then values need to be excluded are filtered in column B.

Step6: Now you can highlight them or remove them per your request. If you want to highlight them, just select the range marked in red in above screenshot, and click Home->Fill Color button to highlight them.

If you want to remove them, just copy this column to another worksheet, and then filter text by color.

Then delete these filtered cells (press F5 to trigger Go To dialog, and then click Special button, check on Visible cells only option and click OK, all visible cells are selected, then you can delete them). Copy and paste updated column to column B again.

## 2. Exclude Values from One Column by COUNTIF Function

Steps are similar with VLOOKUP function.

Step1: Insert a new column between column B and column C. Then in C2 enter the formula:

``=COUNTIF(\$D\$2:\$D\$6,B2)``

COUNTIF function is used for counting how many times B2 appears in range \$D\$2:\$D\$6. If B2 value appears in the selected range, COUNTIF returns the times B2 appears, if B2 doesn’t appear, COUNTIF returns 0.

Step2: Drag down the fill handle till reaching the last cell in column C. Verify that C2:C11 are filled with returned value 1 or 0.

For values in column B, if 1 is displayed in its adjacent cell of column C, this value should be excluded.

Now you can follow step#4-#6 in method1 to exclude them.

## 3. Exclude Values from One Column Based on Another Column with VBA Code

If you want to exclude values from one column based on values in another column with VBA code, and return an array result, you can use the following steps:

Step1: Open the Excel workbook and press ALT + F11 to open the Visual Basic Editor.

Step2: Click on Insert > Module to add a new module to the workbook.

Step3: Paste the following code into the module. Close the Visual Basic Editor and return to the Excel workbook.

```Sub ExcludeValues_excelhow()
Dim rng1 As Range, rng2 As Range, destRange As Range
Dim arr1 As Variant, arr2 As Variant, result() As Variant
Dim i As Long, j As Long, k As Long, flag As Boolean

' Prompt for the first range of values
Set rng1 = Application.InputBox("Select the first range of values to exclude from:", Type:=8)
If rng1 Is Nothing Then Exit Sub

' Prompt for the second range of values
Set rng2 = Application.InputBox("Select the range of values to exclude based on:", Type:=8)
If rng2 Is Nothing Then Exit Sub

' Prompt for the destination cell
Set destRange = Application.InputBox("Select the destination cell:", Type:=8)
If destRange Is Nothing Then Exit Sub

' Convert the input ranges to arrays
arr1 = rng1.Value
arr2 = rng2.Value

' Exclude values based on the second range
ReDim result(1 To UBound(arr1, 1), 1 To 1)
For i = 1 To UBound(arr1, 1)
flag = False
For j = 1 To UBound(arr2, 1)
If arr1(i, 1) = arr2(j, 1) Then
flag = True
Exit For
End If
Next j
If Not flag Then
k = k + 1
result(k, 1) = arr1(i, 1)
End If
Next i

' Resize the result array
'ReDim Preserve result(1 To k, 1 To 1)

' Write the result to the destination cell
destRange.Resize(k, 1).Value = result
End Sub```

Step4: Press ALT + F8 to open the Macros dialog box. Select the “ExcludeValues_excelhow” macro and click on the “Run” button.

Step5: select the first range of values to exclude from.

Step6: select the range of values to exclude based on.

Step7: select the destination cell to place the result.

Step8: The macro will exclude the values from the first range based on the values in the second range and write the result to the destination cell.

## 4. Conclusion

Now you should have a clear understanding of how to implement each of these methods and choose the one that suits your needs best.

## 5. Related Functions

• Excel VLOOKUP function
The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….
• Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…

## How to Count Positive or Negative Numbers in Excel

Whether you are dealing with financial data, scientific data, or any other type of numerical data, it’s crucial to be able to determine how many of the values in your dataset are positive or negative. Excel provides various built-in functions and tools to make this task quick and easy, allowing you to count the number of positive or negative values in a range of cells.

This post will guide you how to count cells that contain positive or negative numbers in Excel. How do I count positive and negative values with a formula in Excel. How to count all negative number or positive number in a given range of cells in Excel.

## 1. Count Only Positive Numbers in Excel

If you want to count the number of cells that contain only positive numbers in a range of cells in Excel, you can create a formula based on the COUNTIF function.
For example, you have a range of cells (A1:C4) that contain negtive and positive numbers, and you want to count only positive numbers, you can use the following formula:

`=COUNTIF(A1:C4,">0")`

Type this formula into a cell, and press Enter key.

You will see that the number of positive values in the given range will be counted.

The COUNTIF function will count the number of cells in the given range that match the supplied criteria. You just need to replace `A1:C4 `with the range of cells you want to count. And “`>0`” means that it will count only values greater than zero, which are positive numbers.

## 2. Count Only Negative Numbers in Excel

If you want to count the number of only negative numbers in the range of cells A1:C4, just use the following formula:

`=COUNTIF(A1:C4,"<0")`

You can also use another formula based on the SUMPRODUCT function to count only negative numbers in the selected range in Excel. Just do the following steps:

Step 1: Select the cell where you want to display the result of the count.

Step 2: type the following formula:

`=SUMPRODUCT(--(A1:C4<0))`

The cell will now display the number of negative numbers in the selected range.

## 3. Count Number of Cells in Excel

If you want to count the number of cells in Excel, you can use the COUNTA function. Type the following formula based on the COUNTA function:

`=COUNTA(Range)`

You need to replace “`Range`” with the range of cells you want to count. For example, if you want to count the range `A1:C4`, just type the following formula in a blank cell:

`=COUNTA(C1:C4)`

## 4. Count Only Numbers in Excel

If you want to count only numbers in Excel, you can use the COUNT function, just type the following formula based on the COUNT function:

`=COUNT(A1:C4)`

Or you can use another formula to achieve the same result of counting only numbers in the selected range cells.

`=SUMPRODUCT(--(ISTEXT(A1:C4)=FALSE))`

## 5. If Cell Contains Negative Number

If you want to check if a cell contains a negative number in Excel, you can use the following formula:

`=IF(A1<0, "Negative", "Not Negative")`

This formula checks the value in cell A1 to see if it’s less than zero (i.e. negative). If it is, the formula will display “`Negative`“. If it’s not negative, the formula will display “`Not Negative`“.

You can replace “`A1`” with the cell reference of the cell you want to check. You can also modify the text that the formula displays when the cell contains a negative number or when it doesn’t.

For example, if you want the formula to display “`Yes`” when the cell contains a negative number and “`No`” when it doesn’t, you can use this formula:

`=IF(A1<0, "Yes", "No")`

## 6. Related Functions

• Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…

## Match Single Criteria in Excel

No repeat statistic based on a single condition is used wildly in daily work, for example, count type of products, the duplicate types are not encountered. This article describes the way of no repeats statistics for data in a single row or a single column using an array formula.

The example below shows the operation on a one-dimensional array with no repeat statistics.

Buy some gifts to reward the top defect submitters for their contributions to the company. The core is to count how many submitters appear on this list with repeated submitters ignored.

## MATCH Function

Build a formula with EXCEL COUNT and MATCH functions:

``=COUNT(1/(MATCH(C2:C7,C:C,0)=ROW(C2:C7)))``

It is an array formula, so press Shift+Control+Enter to run it.

In this formula:

• The “Help Column” shows the logical values returned by the MATCH function.
• The MATCH function determines whether the submitter appears in the list for the first time. It returns logical `TRUE `if it detects that the submitter does not appear in the range from the first cell to the cell above it, or logical `FALSE `if the submitter already appears in the searched field.

Use “`1`” divided by the logical value array to get a new array `{1;1;#DIV/0!;1;1;#DIV/0!}`.

The COUNT function counts the numbers with errors ignored.

## COUNTIF Function

We can also use EXCEL SUM and COUNTIF functions to count without repeats.

Build a formula with EXCEL SUM and COUNTIF functions：

``=SUM(1/COUNTIF(C2:C7,C2:C7))``

It is still an array formula, press Shift+Control+Enter to run it.

In this formula:

• COUNTIF(A:A,A:A) is a classic usage in statistics to count the number of occurrences of each value in column A.
• COUNTIF (C2:C7,C2:C7) returns an array that stores the number of occurrences of each submitter in the list. The array is {3;1;3;1;1;3}.
• Use “`1`” divided by the array returned by COUNTIF function to get a new array `{1/3;1;1/3;1;1;1/3}`.
• The SUM function adds up the numbers in the array. For duplicate values, for example, Andy in the example, `1/3+1/3+1/3=1`, so there are no duplicates in the count, which completes the statistics without duplicates.

## Calculate Win Loss Tie

Suppose you got a task to calculate the win, loss, and tie totals; what would you do? If you are new to Ms Excel and don’t have enough experience with it, then you might do this task manually but let me add that doing this task manually would take a lot of time you will not be able to complete the task at a specific time and you will end up exhausted.

So for calculating a lot of win, loss, and tie totals in a matter of seconds, all you need to do is to read this article thoroughly

So without any further ado, let’s dive into it.

## General Formula

Use the method below to compute win-loss tie totals.

`=SUMPRODUCT(((team1=\$F3)*(score1>score2))+((team2=\$F3)*(score2>score1)))`

## Syntax Explanation

• Parenthesis (): This symbol is used to organize the elements.
• Plus operator (+): This symbol aids in the addition of values.
• SUMPRODUCT: In Excel, this function multiplies the relevant arrays or ranges and returns the total of the products. More information on the SUMPRODUCT Function may be found here.

## Summary

A formula based on the SUMPRODUCT function may be used to determine a team’s win, loss, and tie totals using game data that contains a score for both sides. Type the following formula:

`=SUMPRODUCT(((team1=\$F3)*(score1>score2))+((team2=\$F3)*(score2>score1)))`

Based on the data presented, this formula gives total wins for the “A”  team and set the following are the named ranges: team1 (A3:A12), team2 (B3:B12), score1 (C3:C12), and score2 (D3:D12).

### Let’s See How This Formula Works:

In this example, the purpose is to compute total wins, losses, and ties for each team mentioned in column F. The fact that a team can appear in either column A or B complicates matters significantly; therefore, we must account for this when computing wins and losses.

You may consider utilizing the COUNTIF or COUNTIFS function to solve this problem. However, these methods are limited to dealing with established ranges for criteria. Instead, the example formula uses the SUMPRODUCT function to sum the result of a Boolean logic-based array expression. When a team occurs in column A, we use the following equation within SUMPRODUCT on the left:

=((team1=\$F3)*(score1>score2))

This expression comprises two expressions connected by multiplication (*) to construct AND logic.

=(team1=\$F3) / It will verify that if the team is “A”

The equation on the right determines whether or not the score1 is larger than the score2:

=(score1>score2) / ensure that score1 is larger than the score2.

Because both expressions utilize multiple-valued ranges, they both yield arrays with numerous outcomes. When we rewrite the formula with the arrays returned.

Multiplication (*) is a math operation that, like AND in Boolean algebra, converts TRUE and FALSE values into 1s and 0s. When both of the related values are TRUE, the outcome is 1. In all other cases, the answer is 0. The end result is a single array that looks like the following:

We have identical reasoning on the right side that checks for Team A wins when A is Team 2:

=((team2=\$F3)*(score2>score1)))

Because “A” does not appear as Team 2 in any game, all results are 0.

After evaluating both the left and right sides, we can rewrite the original formula as follows:

`=SUMPRODUCT(({0;1;1;0;0;0;0;0;0;0})+({0;0;0;0;0;0;0;0;0;0}))`

Notice that the arithmetic operator between the two arrays is addition (+), which corresponds to OR logic at this point. We do this because A wins as either Team 1 or Team 2. The result is a single array within SUMPRODUCT:

`=SUMPRODUCT(({0;1;1;0;0;0;0;0;0;0})`

With only one array to process, SUMPRODUCT adds the array’s components and provides a single result, 2.

### Related Functions

• Excel SUMPRODUCT function
The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
• Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
• Excel COUNTIFS function
The Excel COUNTIFS function returns the count of cells in a range that meet one or more criteria. The syntax of the COUNTIFS function is as below:= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)…

## Break ties with helper COUNTIF and column

Suppose you got a task to adjust the values that contain the ties; what would be your first attempt to break the ties of the given value? If you are wondering about doing this task manually, let me add that you will end up exhausted, and there is a probability that you will not complete this task on time. Moreover, besides consuming a lot of time and energy, it also seems to be a cumbersome task to break the ties in the values manually.

But don’t worry about it because you have just landed on the right article, where you will get concise information to break the ties in just a matter of seconds.

So without any further ado, let’s get started.

## General formulation

The formula for breaking ties in values using column and COUNTIF is as follows.

`= (COUNTIF(range,A1)-1)*adjustment + A1`

## Explanation of Syntax

You must be aware of the syntax used in the above formula to use it to complete your task.

CountIF: In Excel, the COUNTIF function counts the number of cells that fulfill a specific condition or criteria. Learning how to utilize the COUNTIF Excel function is helpful because we frequently want to know how many objects in a huge collection are alike in a specific manner. The COUNTIF function is commonly used as a fast fix.

• The plus operator (+): This operator is used to add the values.
• Minus Operator (-): Use this symbol to subtract any two numbers.
• Multiplication (*): In this symbol, any two values or numbers will be multiplied.
• The comma symbol (,): This symbol is a separator that aids in the separation of a list of values.
• Parenthesis (): This symbol’s primary function is to group the elements.

## Summary

To break ties, use a helper column and the COUNTIF function to alter values so that they don’t have duplicates and don’t result in ties. The formula in D5 in the example is:

`= (COUNTIF(\$B\$3: B3, B3)-1)*0.01 +B3`

When you employ SMALL, LARGE, or RANK functions to rank highest and lowest values, you may encounter ties because the data contains duplicates. In this situation, one method for breaking ties is to create a helper column with altered values, then rank those values instead of the originals.

The reasoning used to alter values in this example is random – the first duplicate value will “win,” but you may modify the formula to employ appropriate logic for your specific scenario and use case.

## Explanation

This formula relies on the COUNTIF function and an increasing range to count occurrences of values. COUNTIFS uses the expanding reference to deliver a running count of occurrences rather than a total count for each value:

`=COUNTIF(\$B\$3: B3, B3)`
`= (COUNTIF(\$B\$3: B3, B3)-1)*0.01`

The result is then multiplied by 0.01 after being removed by 1 (which makes the count of all non-duplicate values zero). This is the “adjustment,” It is purposefully minimal to not significantly influence the original value.

“excel” and “ppt” both have the same sales of 500 in the scenario. Because excel is first in the list, the running count of 500 equals 1 and is canceled out by removing 1; thus, the estimate in the helper column remains unchanged:

However, the running count of 500 for ppt product is 2, therefore the estimate is adjusted:

`=B7+0.01`

Finally, instead of the original values in columns G and H, the corrected values are utilized for ranking. The formula in H3.

`=LARGE(\$C\$3:\$C\$8,F3)`

G3’s formula is as follows:

`=INDEX(\$A\$3:\$A\$8,MATCH(H3,\$C\$3:\$C\$8,0))`

## Column of temporary assistance

Suppose you don’t want to use a helper column in the final solution. In that case, you can use it temporarily to get calculated values, then use Paste Special to convert values into the original cell range.

Then you can delete the helper column.

### Related Functions

• Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
• Excel MATCH  function
The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
• Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
• Excel LARGE function
The Excel LARGE function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array. The syntax of the LARGE function is as below:= LARGE (array,nth)…

## Sort/Rank Numeric Values with Duplicate Values Exist

Excel built-in RANK function can sort a set of values. If there are duplicate numbers, then the rank number is also duplicated. See the following example:

There are two numbers “100” in range A2:A9, they are both the third largest number, so there are two “3” in the sort column and there is no number “4”, “4” is occupied by the second “3”.

If we want to avoid this problem (4 should be assigned to the second 100), when we encounter a duplicate number in a set of numbers, instead of assigning it the previously used rank number, we will assign it a new rank number that follows exactly the previous rank number.

See the new sequence:

## FORMULA

As we mentioned above, if you just apply the Excel RANK function to sort the numbers, once you encounter duplicate numbers, the duplicate rank number will be displayed.

When sorting numbers, we need to ensure below three points:

• Sorting numbers refer based on a proper order: ascending order or descending order
• That duplicate numbers are assigned to two numbers which are consecutive
• In Sort column, rank numbers are unique

In this example, the formula is:

`=RANK(A2,\$A\$2:\$A\$9)+COUNTIF(\$A\$2:A2,A2)-1`

Press Control+Shift+Enter to run this formula.

## EXPLANATION

We built this formula with the help of Excel RANK function and COUNTIF function, and also uses addition and subtraction operation.

### RANK EXPLANATION

The RANK function in Excel you can simply understand as rank a given set of values in ascending or descending order.

Syntax:

`=RANK(number, ref, [order])`
• number – rank this number
• ref – a set of numbers for ranking
• order – optional; ascending (1) or descending order (0)

# Ascending order means sorting the largest number as 1

# Descending order means sorting the smallest number as 1

You can set ascending or descending order according to your requirements. For example, in a race, you can set descending order when ranking time, but ascending order when ranking sales. In the example, order option is omitted, so the numbers are sorted in ascending order.

`=RANK(A2,\$A\$2:\$A\$9) – compare the number in cell A2 with the numbers in the range \$A\$2:\$A\$9`

In this example, the numbers are arranged in a fixed range. To avoid the range being adjusted when copying the formula, so we add \$ to lock the range. This formula works well if there are no duplicate numbers, and the RANK function will assign 1 to the largest number and 2 to the second largest number according to the rule, and so on.

Unfortunately, there are duplicate numbers in this example. To break the tie caused by the limitations of the RANK function, we added COUNTIF function to enhance the formula.

### COUNTIF EXPLANATION

Excel COUNTIF function returns the number of cells the meet one condition.

Syntax:

`=COUNTIF(range, criteria)`

In this example, the formula is:

`=COUNTIF(\$A\$2:A2,A2) – returns how many duplicate numbers in the selected range`

When copying the formula downward, the given range \$A\$2:A2 is extended, the starting cell A2 is locked, and the ending cell is the cell where the RANK function is currently working. Therefore, if we encounter a duplicate number in the range, the COUNTIF function will return the number of times the number is duplicated.

• If number only appears once, COUNTIF returns 1 as current row is included in selected range;
• If number appears twice, COUNTIF returns 2, and so on;
`=COUNTIF(\$A\$2:A2,A2)-1`

Since the number appears at least once in the selected range, for example, COUNTIF(\$A\$2:A2,A2) returns 1, we use the subtraction operation “-1” to offset the value returned by COUNTIF.

### FORMULA EXPLANATION

We take number “100” in cell A2 as an example, in B2 enter below formula:

`=RANK(A2,\$A\$2:\$A\$9)+COUNTIF(\$A\$2:A2,A2)-1`

Convert cell or range reference to actual number or array, the formula is:

`=RANK(100,{100;50;75;100;150;125;50;85})+COUNTIF(100,100)-1`

As number “100” is the third largest number in the array, so “3” is returned by RANK function; COUNTIF function returns “1”, so the result is “3+1-1=3”.

We can see that the formula returns “4” for the same number “100” located in cell A5. This time COUNTIF function plays an important role and breaks tie perfectly.

In B5, the formula is:

`=RANK(A5,\$A\$2:\$A\$9)+COUNTIF(\$A\$2:A5,A5)-1`

RANK returns 3:

`-> 3+COUNTIF({100;50;75;100},100)-1`

COUNTIF returns 2 because {100;50;75;100} contains two “100”:

`-> 3+2-1 -> 4`

### Related Functions

• Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
• Excel Rank function
The Excel RANK function returns the rank of a given number in a supplied range of cells.  If there are duplicated values in a list of values, it will be set the same rank.The syntax of the RANK function is as below:= RANK (number,ref,[order])….

## Count Cells that are Case Sensitive

If you are a frequent user of Microsoft Excel, you may have come across situations where you needed to add or count the cells that were case-sensitive. If you have done this task manually, I would like to point out that it is the most inefficient way to do it, particularly when MS Excel has the versatility to do it in a matter of seconds. As a result of attentively reading this article, you will learn about the many adding or counting case-sensitive cells.

Simple generic formula:

```=SUMPRODUCT(--ISNUMBER(FIND(value, range)))
```

## Summary

You may use a formula depending on the ISNUMBER and FIND functions and the SUMPRODUCT function to calculate the number of cells that contain specified text (i.e., hold a substring), taking into consideration upper and lower case. D1 in the example displayed has the following formula, which has been written down:

`=SUMPRODUCT(--ISNUMBER(FIND(C1,\$A\$1:\$A\$6)))`

## Let’s See How This Formula Works

In this example, the purpose is to count the number of times each code appears as a substring in a case-sensitive manner, as shown below. When calculating text values, the functions COUNTIF and COUNTIFS are excellent choices. However, since these functions are not case-sensitive, they cannot be utilized to address this issue. To check for substrings, the FIND function should be used in conjunction with the ISNUMBER function, and the SUMPRODUCT function should be used to sum up, the results.

`= FIND(C1,\$A\$1:\$A\$6)`

The FIND function is case-sensitive and accepts three parameters: find text, within the text, and start num. It is also case-sensitive and takes three arguments. Here is the text we are searching for, and this is the text that we are searching inside. Start num is the number of characters to begin searching in find text. Because start num defaults to 1, we are not supplying a number in this instance. This is necessary since we always want FIND to begin with the first character. When finding the text is discovered inside within text, FIND returns the position of the found text as a number: When finding the text is located within text, FIND returns the position of the found text as a number:

Note: When the find text function fails to yield a result, FIND returns the #VALUE! Error.

`=ISNUMBER(FIND(C1,\$A\$1:\$A\$6))`

This implies that we may be using the ISNUMBER function to transform the output of the FIND function into a TRUE or FALSE value using the ISNUMBER function. A TRUE value will arise from any number, while a FALSE value will result from any error:

As demonstrated in the example, we have four substrings in column C and a variety of codes in columns A1:A6, representing the data for the designated range. Specifically, we want to know how many times each substring in C1:C4 occurs in A1:A6, and we want to know how many times each substring is case-sensitive.

### Related Functions

• Excel SUMPRODUCT function
The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
• Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
• Excel COUNTIFS function
The Excel COUNTIFS function returns the count of cells in a range that meet one or more criteria. The syntax of the COUNTIFS function is as below:= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)…
• Excel ISNUMBER function
The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
• Excel FIND function
The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…

## Check Dates in chronological order

Assume you have a date list that has different date formats, as seen in the accompanying picture. In this instance, Excel’s Sort function will fail to sort them appropriately. However, you may convert all various date formats to a particular format, such as mm/dd/yyy, and then arrange them chronologically. This post will demonstrate how to sort dates in chronological order and how to check if a range of dates are in chronological order.

## Explanation

This is an excellent illustration of how the SUMPRODUCT function may be used to overcome the inability of the COUNTIF or COUNTIFS functions to operate. In this scenario, the purpose is to verify that all dates within a certain range are in chronological order and to display a “YES” value only when dates are in chronological order.

The argument is pretty straightforward, although maybe not apparent. Rather of ensuring that all dates are bigger than the previous one, we determine if any prior date is greater than the next. If we discover even one, the dates will be out of sequence. They are if we discover none (zero).

At its heart, this formula uses the SUMPRODUCT function to determine the number of dates in A1:C1 that are larger than or equal to B1:D1:

`=SUMPRODUCT(--(A1:C1>=B1:D1))`

Due to the fact that we are employing the larger than or equal to (>=) operator, successive duplicate dates will be disallowed.

This is a two-range operation with 4 dates in each range. As a consequence, an array of TRUE and FALSE values is created as follows:

You can use the following formula based on the IF function, the SUMPRODUCT function, type:

`=IF(SUMPRODUCT(--(A1:C1>=B1:D1)) =0,”YES”,””)`

In Excel 365, the SORT function offers a convenient alternate solution:

`=IF(SUM(--(A1:D1<>SORT(A1:D1,1,1,1)))=0,"YES","")`

The dates in A1:D1 are compared to the same dates following SORT sorting. As with the original algorithm, we are counting any instance when a date is not same (i.e. any date moved by SORT). If there are no dates that vary, a “YES” String Value is returned.

In contrast to the original formula, the SORT version does not check for blank (empty) fields or duplicate dates automatically. The following version has an extra check for blank cells:

`=IF(SUM(((A1:D1<>SORT(A1:D1,1,1,1)))+( A1:D1=""))=0,"YES","")`

In Boolean Algebra, the addition (+) operator behaves similarly to the OR logic. Because the math operation converts TRUE and FALSE values to 1s and 0s automatically, we no longer need the double negative (–).

Note: we use SUM rather than SUMPRODUCT here since Excel 365’s dynamic array support enables SUM to perform array operations natively, without the need for control + shift + enter.

### Related Functions

• Excel SUM function
The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
• Excel SUMPRODUCT function
The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
• Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
• Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
• Excel Sort function
The SORT function in Excel sorts the contents of an array or range alphabetically or numerically by columns or rows.The syntax:=SORT(array, [sort index],) …
• Excel COUNTIFS function
The Excel COUNTIFS function returns the count of cells in a range that meet one or more criteria. The syntax of the COUNTIFS function is as below:= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)…

## Check If a Cell is Blank or Empty

The article demonstrates how to check if a range of cells is blank or empty by ISBLANK and other functions to recognize empty cells in Excel and take appropriate action based on their status.

There are several circumstances in which you need to determine if a cell is empty or not. If a cell is blank, for example, you may want to add, count, copy a value from another cell, or do nothing. ISBLANK is the appropriate function to employ in these situations, occasionally alone, but most often in conjunction with other Excel functions.

## ISBLANK in Excel – a few points to keep in mind

The critical item to remember is that the ISBLANK function in Excel recognizes actually empty cells, that is, cells that contain nothing: no spaces, no tabs, no carriage returns, or anything else that looks blank in a view.

ISBLANK returns FALSE for a cell that seems to be blank but is not. If a cell includes any of the following, this behavior occurs:

`=IF(A1>"", A1, "") //yields an empty string`

Imported zero-length string from an external database or as a consequence of a copy/paste operation.

Spaces, apostrophes, non-breaking spaces (&nbsp);, linefeeds, and other non-printing characters are permitted.

## How to utilize the ISBLANK function in Excel

To obtain a better grasp of the ISBLANK function‘s capabilities, consider these practical applications.

### If a cell is blank in Excel, then

Because Microsoft Excel does not have an IFBLANK function, you must use IF and ISBLANK to test a cell and execute an action if it is empty.

The general version is as follows:

`=IF(ISBLANK(B1), "open", "completed")`

To demonstrate, let’s verify whether a cell in column B (delivery date) has any data. If the cell is empty, output “Open”; if it is not empty, produce “Completed”.

Please keep in mind that the ISBLANK function only returns cells that are completely blank. ISBLANK returns FALSE if a cell contains anything invisible to the human eye, such as a zero-length string.

### If any cell in range is blank, then do something

There are many techniques to check a range of cells in Microsoft Excel for empty cells. We will use an IF statement to output one value if the range has at least one empty cell and another value if the range contains no empty cells. The logical test is performed by calculating the total number of empty cells in the range and then determining if the count is larger than zero. This may be accomplished via the use of the COUNTBLANK or COUNTIF functions.

### Related Functions

• Excel ISBLANK function
The Excel ISBLANK function returns TRUE if the value is blank or null.The syntax of the ISBLANK function is as below:= ISBLANK (value)…
• Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
• Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
• Excel COUNTBLANK function
The Excel COUNTBLANK function use to count the number of empty cells in a range of cells. So you can use the COUNTBLANK function to get the number of blank cells in a given range in Excel.The syntax of the COUNTBLANK function is as below:= COUNTBLANK (range)…

## Extract matching values From Two Lists

Suppose that you are working with two lists containing few values, and you want to extract the matching values from those two lists into another separate list. You might prefer to manually extract the matching values from the two lists, which is ok for the few values, but it would be a big deal to extract the matching values from the two lists having multiple cells, and doing it manually would be a foolish attempt because there are 90% chances that you would 100% get tired of it and would never complete your work on time.

But don’t be worry about it because after carefully reading this article, extracting the matching values from the two lists into another separate list would become a piece of cake for you.

So let’s dive into the article to take you out of this fix.

## General Formula

The Following Formula would help you compare and extract the matching values from the two lists into another separate list.

`=FILTER(table1,COUNTIF(table2,table1))`

This Formula is based on the FILTER and COUNTIF functions, where the `table1(A2:A9)` and `table2 (B2:B8)` are the named ranges, the list in the `range (D2:D6)` is the list containing the matching values by comparing both table1 and table2.

## Syntax Explanations

Before going into the explanation of the Formula for getting the work done efficiently, we must understand each syntax which would make it easy for you that how each syntax contributes to executing the matching values from the two lists into another separate list:

• `Filter`: This function contributes to narrowing down or filtering out a range of data on the user-defined criteria.
• `COUNTIF`: It is a statistical function that counts the number of cells to meet specific criteria.
• `List`: In this Formula, the list represents the two lists present in the excel worksheet to execute the common values.
• `Comma symbol (,)`: In Excel, this comma symbol acts as a separator that helps to separate a list of values.
• `Parenthesis ()`: The core purpose of this Parenthesis symbol is to group the elements and separate them from the rest of the elements.

## Let’s See How This Formula Works

The FILTER function is used in this Formula to extract data based on a logical test created using the COUNTIF function:

`=FILTER(table1,COUNTIF(table2,table1))`

The COUNTIF function is being used to generate the actual filter.

`=COUNTIF(table2,table1))`

It’s worth noticing that we’re using table2 as the range argument and table1 as the criterion argument. In other words, we’re asking COUNTIF to count all values in table1 that occur in table2. We obtain an array with several results since we provide COUNTIF various values for criteria:

`{1;2;0;1;0;0;0;1}`

Note that the array has 8 counts for each element in the table1. A zero value denotes a value in a table1 that is not present in the table2. Any other positive number denotes a value in table1 that is also present in table2. As the include parameter, this array is passed straight to the FILTER function:

`=FILTER(table1,{1;1;0;1;0;1;0;0;1;0;1;1})`

The array is used as a filter by the filter function. Any item in the table1 connected with a zero would be eliminated, but any value related to a positive number would retain.

## Extract Non-matching Values

If you want to remove the non-matching values from table1, values in table1 that do not present in table2, we would need to modify the above formula, which is stated as follows:

`=FILTER(table1,NOT(COUNTIF(table2,table1)))`

The NOT function reverses the COUNTIF result, and every non-zero integer returns FALSE, and any zero value returns TRUE. The output is a list of all the values in the table1 that aren’t on the table2.

## Method two: Extract Matching Values Using INDEX

You can also create a newly formula to extract matching values without the FILTER function, but the Formula would become more complicated. And the formula based on the INDEX function. Like below:

`=IFERROR(INDEX(table1,SMALL(IF(COUNTIF(table2,table1),ROW(table1)-ROW(INDEX(table1,1,1))+1),ROWS(\$D\$2:D2))),"")`

Except in Excel 365, this array formula must be typed using `control + shift + enter`.

The INDEX function, which takes table1 as an array parameter, lies at the heart of this Formula. The majority of the remaining Formula determines the row number for matching values. This expression creates a list of relative row numbers as follows:

`=ROW(table1)-ROW(INDEX(table1,1,1)) +1`

which yields a 12-number array reflecting the rows in table1:

`{1;2;3;4;5;6;7;8;}`

These are filtered using the IF function and the same methodology used above in the FILTER Function, but this time using the COUNTIF function:

`=COUNTIF(table2,table1) / identify values that match`

`=IF(COUNTIF(table2,table1),ROW(table1)-ROW(INDEX(table1,1,1))+1)`

The resultant array is as follows:

`{1;2;FALSE;4;FALSE; FALSE;7;8; }`

As the Formula is copied along the column, this array is sent immediately to the SMALL function.

The IFERROR function is designed to catch mistakes when a formula is copied down, and the matching values run out.

### Related Functions

• Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
• Excel Filter function
The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…
• Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
• Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
• Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
• Excel SMALL function
The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
• Excel IFERROR function
The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)….
• Excel NOT function
The Excel NOT function returns the opposite of a given logical or Boolean value. For example, if you supplied with the value TRUE, the NOT function will return FALSE; If you supplied with the value FALSE, and the NOT function will TRUE. The syntax of the NOT function is as below:=NOT(logical)…