CountIFS - ignore blank cells and cells with text in

Hi Guru's
I have an excel workbook and wish to count the number of unique values across various sheets.I have the following which works until the value in column B of each sheet is either blank or has a text prefix to the number.

=SUMPRODUCT((1/COUNTIFS('ORANGE SHIFT'!B11:B21,'ORANGE SHIFT'!B11:B21,'LIME SHIFT'!B11:B21,'LIME SHIFT'!B11:B21)))

My question is :
How do i make it ignore the blank cells and the cells with a text prefix in column B?

Many thanks in advance for your assistance.

Hello whitebits and Welcome to Excel Forum.
I can not replicate the counting of unique numbers in the range B11:B21 on different sheets using the formula in post #1. I feel that I would need to be able to do that before I can proceed to modifying the formula to ignore blanks and text entries.
It might help us to help you if you would upload a sample workbook that shows how the formula works. To upload a sample workbook click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

I feel as if the formula is yielding a count of the unique values on the Lime Shift sheet only. If you run the Evaluate Formula tool you see that the 2's in the array align with values that are repeated in the range Lime Shift! B11:B21
Also if the formula were counting the values from both sheets it would seem that 111 would have a count of 3, and the Evaluate Formula tool doesn't show any value as having a count of 3.
Another check would be to put 999 in one of the cells in the range Orange Sheet! B11:B21, the count on the result sheet doesn't change although there should now be 10 unique values.
Let us know if I have misunderstood the way the formula is working.

In cell B20 on the Orange Shift sheet I replaced the value with 999. The unique values taken from the lists on both sheets are 111, 222, 333, 444, 555, 666, 777, 888, 999 and 1, however cell C4 on the results sheet displays 9.
One way to find the unique values is to use the following array entered formula*, as modeled in E4:E14 on the Results Sheet:

Formula:

Please Login or Register  to view this content.

Cell C10 on the results sheet then counts the values in list.
If this is something that you would be interested in, then we can begin to look at ways to exclude blanks and zeros in the two original lists.
*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Hi all- This ARRAY FORMULA* counts unique non-zero numeric values across both sheets, ignoring blanks and text:

Please Login or Register  to view this content.

*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.