Forum Discussion
Using Countif to create a Lookup column
=D3&COUNTIF($D$2:D3,D3). I am using this formula to concatenate pipe size with its nth incidence. The formula works fine except when reading numbers such as 10 or 20 that end with a zero.
The issue with your formula arises when counting occurrences of pipe sizes ending in zero. The COUNTIF function considers numbers and text identically, leading to miscounting for values like "10" or "20". Here's a corrected formula that addresses this:
Excel=D3 & TEXT(COUNTIF($D$2:D3, LEFT(D3, LEN(D3)-1) & "*"), "0")
Explanation:
- LEFT(D3, LEN(D3)-1): Extracts all characters except the last digit from D3. For "10", it returns "1".
- "*": Appends a wildcard character to the extracted prefix, making the pattern "1*".
- COUNTIF($D$2:D3, LEFT(D3, LEN(D3)-1) & "*"): Counts the occurrences of pipe sizes that start with the characters in D3 followed by any digit using the wildcard.
- TEXT(..., "0"): Converts the count to text format, ensuring leading zeros are preserved (e.g., "1" instead of "1").
- &: Concatenates the pipe size (D3) with the formatted count, resulting in the desired output (e.g., "10|1").
This formula effectively counts occurrences based on the pipe size's prefix, excluding the trailing zero, and displays the count with leading zeros when necessary.
10 Replies
- When I opened the file the problem fixed itself, because when I enter a "10" in the pipe size column the concatenation works. However, the problem moved to another column where vlookup is being used. Here's the formula:
=IFERROR(VLOOKUP($D4,$AA$19:$AW$42,G4,FALSE),"")
Any feedback on how to use the correction below is appreciated.
Sincerely,
David The issue with your formula arises when counting occurrences of pipe sizes ending in zero. The COUNTIF function considers numbers and text identically, leading to miscounting for values like "10" or "20". Here's a corrected formula that addresses this:
Excel=D3 & TEXT(COUNTIF($D$2:D3, LEFT(D3, LEN(D3)-1) & "*"), "0")
Explanation:
- LEFT(D3, LEN(D3)-1): Extracts all characters except the last digit from D3. For "10", it returns "1".
- "*": Appends a wildcard character to the extracted prefix, making the pattern "1*".
- COUNTIF($D$2:D3, LEFT(D3, LEN(D3)-1) & "*"): Counts the occurrences of pipe sizes that start with the characters in D3 followed by any digit using the wildcard.
- TEXT(..., "0"): Converts the count to text format, ensuring leading zeros are preserved (e.g., "1" instead of "1").
- &: Concatenates the pipe size (D3) with the formatted count, resulting in the desired output (e.g., "10|1").
This formula effectively counts occurrences based on the pipe size's prefix, excluding the trailing zero, and displays the count with leading zeros when necessary.
smylbugti222gmailcom =IF(D25=0,"",D25 & TEXT(COUNTIF('90 DEG NONREPAD NOZZLE WELDS'!$D$2:D25, LEFT(D25, LEN(D25)-1) & "*"), "0")) Copying the formula produced these results
1"1, 1"2, 10"1, 1"4. With this formula the number ten is being treated as a one. Do you have a solution for this?
- I am building an entire set of templates for our estimating program. Each summary page that I build will perform a vlookup using the results of the formula you have provided. In the industrial world 10" pipe are common, and so it is critical that a 10 is treated as such instead of a 1 every time.
smylbugti222gmailcom I am attempting to replace the 0 output with a dash. Any suggestions?
- The output seems to be unformattable.
smylbugti222gmailcom The reason the first problem seemed to correct itself is because I used O instead of 0. I reverted back to using 0 and the first problem still persists. So, I used the formula provided, but nothing changed. Is there something I am missing?
davidclubb Can you pls share a screenshot of the error or a link to view the file ? You can DM also
- =IF(D25=0,"",D25 & TEXT(COUNTIF('90 DEG NONREPAD NOZZLE WELDS'!$D$2:D25, LEFT(D25, LEN(D25)-1) & "*"), "0")). D25 is the last cell in the range of cells. i want to extend the range into another nonconsecutive cell.
