Counting duplicate without Counting Blank(Formula) cells

[Rodney2485]

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

[#2]

@Rodney2485 

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.

[#3]

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

[#4 · Accepted solution]

@Rodney2485 

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.

[#5]

@NikolinoDE  Thank you, this worked perfectly!

[#6]

yw