Forum Discussion
Using Countif to create a Lookup column
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.
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:
=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?
smylbugti222gmailcom Thanks for the feedback. I am looking for help on how to place your solution into the second formula provided.
