Forum Discussion
Solved
Counting duplicate without Counting Blank(Formula) cells
I want to Count how many times a specific number in a cell is duplicated without counting cells that look blank but contain a formual. I'm trying to return a blank or a zero in the Order Qty Row is...
If you want to count the occurrences of a wave number in Column C and return a value in the "Order Qty" column without counting the empty string cells, you can use the following formula:
=IF(AND(COUNTIF($C$2:$C$100, C2) > 1, C2 <> ""), COUNTIFS($C$2:$C$100, C2, D2:D100, "<>"), "")
Here's what each part of the formula does:
- COUNTIF($C$2:$C$100, C2) > 1: Checks if the wave number in the current row appears more than once in Column C.
- C2 <> "": Checks if the current cell in Column C is not empty (doesn't contain an empty string).
- COUNTIFS($C$2:$C$100, C2, D2:D100, "<>"): Counts the occurrences where the wave number is the same in Column C and the corresponding cell in Column D is not blank.
The IF statement combines these conditions. If the wave number is duplicated, and the current cell in Column C is not empty, it returns the count of occurrences from the "Order Qty" column; otherwise, it returns an empty string.
Adjust the ranges according to your actual data.
NikolinoDE Still counting empty strings. Let me try to be more specific. I need to know how many times a wave Number is duplicated in Column C and return a value in the Order Qty column without counting the empty string cells. The problem with your forumla is that each pick ticket is never repeated.
If you want to count the occurrences of a wave number in Column C and return a value in the "Order Qty" column without counting the empty string cells, you can use the following formula:
=IF(AND(COUNTIF($C$2:$C$100, C2) > 1, C2 <> ""), COUNTIFS($C$2:$C$100, C2, D2:D100, "<>"), "")
Here's what each part of the formula does:
- COUNTIF($C$2:$C$100, C2) > 1: Checks if the wave number in the current row appears more than once in Column C.
- C2 <> "": Checks if the current cell in Column C is not empty (doesn't contain an empty string).
- COUNTIFS($C$2:$C$100, C2, D2:D100, "<>"): Counts the occurrences where the wave number is the same in Column C and the corresponding cell in Column D is not blank.
The IF statement combines these conditions. If the wave number is duplicated, and the current cell in Column C is not empty, it returns the count of occurrences from the "Order Qty" column; otherwise, it returns an empty string.
Adjust the ranges according to your actual data.
NikolinoDE Thank you, this worked perfectly!
