Forum Discussion
Rodney2485
Jan 12, 2024Brass Contributor
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...
- Jan 13, 2024
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
Jan 13, 2024Gold Contributor
To count the occurrences of a specific number in a range while excluding cells that look blank but contain a formula, you can use the following formula:
=COUNTIFS(A2:A100, "5000094270", B2:B100, "<>")
Here, we assume your data is in columns A and B from row 2 to row 100. You need to adjust the range according to your actual data.
This formula counts the occurrences where "5000094270" appears in column A, and the corresponding cell in column B is not blank (does not contain an empty string ""). It effectively excludes cells that appear blank due to a formula.
If you want to return a blank or zero in the "Order Qty" row when there are no occurrences, you can modify the formula like this:
=IF(COUNTIFS(A2:A100, "5000094270", B2:B100, "<>") > 0, COUNTIFS(A2:A100, "5000094270", B2:B100, "<>"), "")
This formula checks if there are occurrences. If there are, it returns the count; otherwise, it returns an empty string (""). Adjust the ranges based on your actual data. The text and steps were edited with the help of AI. If these steps not helped you, I recommend adding more information to your topic. Information such as Excel version, operating system, storage medium, file extension, etc. In this link you will find some more information about it: Welcome to your Excel discussion space!
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
- Rodney2485Jan 13, 2024Brass Contributor
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.
- NikolinoDEJan 13, 2024Gold Contributor
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.
- Rodney2485Jan 13, 2024Brass Contributor
NikolinoDE Thank you, this worked perfectly!