SOLVED

# Counting duplicate without Counting Blank(Formula) cells

Copper 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 isntead of the 1835 i'm getting.

 Pickticket Wave # Ship # Ship Via Customer Ship to Shipped Qty Order Qty 5000105278 20231229094 950564 TFIN OZARK AUTOMOTIVE WHSE SPRGFLD 70 3 5000094270 884638 SLDD GARNETT AUTO SUPPLY 1 670 1835
5 Replies

# Re: Counting duplicate without Counting Blank(Formula) cells

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, "<>"), "")

My answers are voluntary and without guarantee!

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

# Re: Counting duplicate without Counting Blank(Formula) cells

@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.

best response confirmed by Rodney2485 (Copper Contributor)
Solution

# Re: 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.

# Re: Counting duplicate without Counting Blank(Formula) cells

@NikolinoDE  Thank you, this worked perfectly!

# Re: Counting duplicate without Counting Blank(Formula) cells

yw
1 best response

Accepted Solutions
best response confirmed by Rodney2485 (Copper Contributor)
Solution

# Re: 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.