Forum Discussion
Counting duplicate without Counting Blank(Formula) cells
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.
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!
