Excel Magic Trick 1043: Count Unique Numbers in Column with Empty Cells and Text (Mixed Data)

See 5 formulas and timing results for counting unique list of numbers in a column with numbers, empty cells and text. Array formula that does not require Ctrl + Shift + Enter that uses the functions:
1. SUM, IF and FREQUENCY
2. SUM, SIGN and FREQUENCY (thanks to Leonid Koyfman from YouTube)
3. SUMPRODUCT, FREQUENCY, comparative array operation, math array operation
4. SUM, FREQUENCY, comparative array operation, math array operation (Amazingly it does not require Ctrl + Shift + Enter.
5. SUMPRODUCT, COUNTIF and ISNUMBER
6. Timing Results for each formula
7. Shout Out to Our Online Excel Team: Have you ever seen a formula like this: =SUM(--(FREQUENCY(A2:A15,A2:A15) Greater Than0)) that does not require Ctrl + Shift + Enter!?!?!?

[excelisfun]

I am glad that you "liked" the video!!

[tjones_aiservices]

Nice videos, this is exactly what I was looking for!, outstanding teaching skills!!!

[krn]

I think I like the SIGN formula method. Not sure why though. Just like it. Thanks Mike.

[ExcelStrategy]

Looking at the formulas visually it apperared to me that the one with the "SIGN" function should be the fastest one but they all have similar timing really surprising !

[leanda]

Or point me in the right direction? I'm really enjoying your work. Keep up the good work! Oh by the way do you think I'd just need to know how to use functions such as sort and pivot tables?

[excelisfun]

For back and forth dialog to get Excel solutions THE best Excel question site is:

mrexcel. com/forum

[leanda]

Hi, I have a few questions for you. I have an interview coming up in the next few days and I have been told that I will undertake a statistical / excel test. The role is for the local council supporting the fostering and adoption team so I'm guessing they just want to make sure I have basic / intermediate excel skills but as I have never taken a test of this nature I wondered if you'd be able to help? Any info you can offer me would be greatly appreciated. Can you recommend any of ur vids?

[chessmove]

Thanks for replying back....
All the numbers are in a single column.
Yes... the numbers appear as 012
Yes... they can repeat within a cell
No... (344 or 334) is not what I'm looking for
Yes... (344, 443, 434) same dights, but different order. This is what i'm looking for
What formula can i use to single out those three sets of numbers.

[JESSICALOPEZ-zrtv]

I've been searching thru your videos and maybe you can help me I have a LONG price list for a furniture catalog that I'm trying to sort a column which is for the page#'(s) that each item# appears on. So a cell may look like this 10, 100, 425.
How can I sort this column to start with the first number in each cell? If you have a video already posted I'd be grateful for any help since I'm new to excel and have ALOT of things I use it for but repetitive things take me forever. Thank you!!

[mhutku]

for reasons i can not figure, i am constantly getting "There is a problem with this formula" error.

I am using excel 2016 and trying to use this formula (or pretty much any of the formula above):

=SUM(IF(FREQUENCY(A2:A1500, A2:A1500), 1))


there are only plain 14-digit numbers (product numbers) in the cells A2...A1500. i merely want a count of the unique product numbers there and i want the result to appearing in cell N1

please help!

[iamgodsgal]

Hello, Can you help me? I am using the following formula to count the number of times a month and year appear in a column on a spreadsheet =SUM(IF(MONTH($E$2:$E$375)=MONTH(P8), IF(YEAR($E2:E$375)=YEAR(P8), 1, 0), 0))
The formula works as long as there are dates in each cell with in the range but when it is blank the #value error is noted. Does anyone have any suggestions on how to use the formula this is being used to calculate the number of tasks received and completed monthly..

[excelisfun]

I dont know. Try:

mrexcel [dot] com/forum