Counting the Number of Cells with the Last Pair of Digits Greater than the First in Excel
In this article, we will learn how to count the number of cells in a range where the last pair of digits is greater than the first pair of digits in Excel. We will cover the following topics:
- Understanding the problem
- Creating a helper column
- Counting the cells
- Using a formula to count the cells
- Subtotals and filtering
Understanding the Problem
Suppose we have a range of cells, A1:A10, with values like "46 64", "63 60", and so on. Each value has two pairs of digits separated by a space. We want to count the number of cells where the last pair of digits is greater than the first pair of digits.
Creating a Helper Column
To solve this problem, we will create a helper column, B, to extract the two pairs of digits from each cell in column A. We will use the following formula in cell B1:
=--TEXT(MID(A1,1,2),"00")&" "&--TEXT(MID(A1,4,2),"00")This formula extracts the first two digits from cell A1 using the MID function and converts them to a number using the -- operator. It then concatenates a space and the last two digits, which are also extracted and converted to a number.
We can then copy this formula down to cells B2:B10.
Counting the Cells
Now that we have a helper column with the two pairs of digits, we can count the number of cells where the last pair is greater than the first. We will use the following formula in cell C1:
=SUMPRODUCT((--RIGHT(B1:B10,2)&LEFT(B1:B10,2))>0)This formula converts each pair of digits in column B to a number and concatenates them. It then checks if the last pair is greater than the first by comparing them as strings. The SUMPRODUCT function then adds up the TRUE/FALSE values, which are treated as 1/0, to give us the count of cells where the last pair is greater than the first.
Using a Formula to Count the Cells
If we don't want to use a helper column, we can use a single formula to count the cells where the last pair is greater than the first. We will use the following formula in cell C1:
=SUMPRODUCT((--RIGHT(MID(A1:A10,FIND(" ",A1:A10)+1,2),2)&LEFT(MID(A1:A10,1,FIND(" ",A1:A10)),2))>0)This formula uses the MID and FIND functions to extract the two pairs of digits from each cell in column A. It then converts them to numbers and concatenates them, just like the previous formula. The SUMPRODUCT function then adds up the TRUE/FALSE values to give us the count of cells where the last pair is greater than the first.
Subtotals and Filtering
We can also use subtotals and filtering to count the number of cells where the last pair is greater than the first. We will first add a subtotal to column B by selecting cells B1:B10 and clicking on the Subtotal button in the Data tab. We will then select the Count function and group by the two pairs of digits in column B.
We can then filter column B to show only the cells where the last pair is greater than the first. We will do this by clicking on the filter button in the Data tab and selecting the filter icon for column B. We will then select the "Last > First" filter option.
The count of cells will then be displayed in the subtotal row at the bottom of the table.
- We have learned how to count the number of cells in a range where the last pair of digits is greater than the first pair of digits in Excel.
- We have used a helper column to extract the two pairs of digits from each cell and concatenate them.
- We have used the SUMPRODUCT function to add up the TRUE/FALSE values and give us the count of cells where the last pair is greater than the first.
- We have used a single formula to extract and concatenate the two pairs of digits without using a helper column.
- We have used subtotals and filtering to count the number of cells where the last pair is greater than the first.