{"id":10459,"date":"2025-09-22T13:29:34","date_gmt":"2025-09-22T20:29:34","guid":{"rendered":"https:\/\/gantovnik.com\/bio-tips\/?p=10459"},"modified":"2025-09-22T13:29:34","modified_gmt":"2025-09-22T20:29:34","slug":"463-count-unique-values-in-excel-with-one-powerful-excel-formula","status":"publish","type":"post","link":"https:\/\/gantovnik.com\/bio-tips\/2025\/09\/463-count-unique-values-in-excel-with-one-powerful-excel-formula\/","title":{"rendered":"#463 Count Unique Values in Excel with One Powerful Excel Formula"},"content":{"rendered":"<p>Ever wonder how to count the number of unique numbers in a column without using PivotTables or helper columns?<br \/>\nHere\u2019s a classic Excel array formula that does the job beautifully:<\/p>\n<pre class=\"lang:vb decode:true \">=SUM(IF(FREQUENCY(A:A, A:A) &gt; 0, 1))<\/pre>\n<h4 data-start=\"381\" data-end=\"400\">How It Works<\/h4>\n<ul data-start=\"401\" data-end=\"745\">\n<li data-start=\"401\" data-end=\"556\">\n<p data-start=\"403\" data-end=\"556\"><strong data-start=\"403\" data-end=\"426\">FREQUENCY(A:A, A:A)<\/strong><br data-start=\"426\" data-end=\"429\" \/>Returns an array where only the <em data-start=\"463\" data-end=\"470\">first<\/em> occurrence of each number shows how many times it appears. All duplicates return 0.<\/p>\n<\/li>\n<li data-start=\"558\" data-end=\"653\">\n<p data-start=\"560\" data-end=\"653\"><strong data-start=\"560\" data-end=\"579\">IF( \u2026 &gt; 0 , 1 )<\/strong><br data-start=\"579\" data-end=\"582\" \/>Converts every non-zero count into a single 1, ignoring duplicates.<\/p>\n<\/li>\n<li data-start=\"655\" data-end=\"745\">\n<p data-start=\"657\" data-end=\"745\"><strong data-start=\"657\" data-end=\"669\">SUM( \u2026 )<\/strong><br data-start=\"669\" data-end=\"672\" \/>Adds those 1\u2019s to give you the total number of distinct numeric values.<\/p>\n<\/li>\n<\/ul>\n<h4 data-start=\"747\" data-end=\"761\">Example<\/h4>\n<p data-start=\"762\" data-end=\"885\">If column <strong data-start=\"772\" data-end=\"777\">A<\/strong> contains <code>5, 5, 7, 7, 7, 9<\/code>, the formula returns <strong data-start=\"827\" data-end=\"832\">3<\/strong>\u2014because there are three unique numbers: 5, 7, and 9.<\/p>\n<h4 data-start=\"887\" data-end=\"896\">Tips<\/h4>\n<ul data-start=\"897\" data-end=\"1071\">\n<li data-start=\"897\" data-end=\"945\">\n<p data-start=\"899\" data-end=\"945\">Works on <strong data-start=\"908\" data-end=\"924\">numbers only<\/strong> (text is ignored).<\/p>\n<\/li>\n<li data-start=\"946\" data-end=\"974\">\n<p data-start=\"948\" data-end=\"974\">Blank cells are skipped.<\/p>\n<\/li>\n<li data-start=\"975\" data-end=\"1071\">\n<p data-start=\"977\" data-end=\"1071\">In Excel 365\/2021 you can enter it normally; in older versions press <strong data-start=\"1046\" data-end=\"1070\">Ctrl + Shift + Enter<\/strong>.<\/p>\n<\/li>\n<\/ul>\n<p data-start=\"1073\" data-end=\"1220\">With this single formula, you can quickly spot how many unique numeric entries are in any dataset\u2014perfect for dashboards, reports, or quick checks.<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Ever wonder how to count the number of unique numbers in a column without using PivotTables or helper columns? Here\u2019s a classic Excel array formula that does the job beautifully: =SUM(IF(FREQUENCY(A:A, A:A) &gt; 0, 1)) How It Works FREQUENCY(A:A, A:A)Returns an array where only the first occurrence of each number shows how many times it [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"nf_dc_page":"","_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","_lmt_disableupdate":"yes","_lmt_disable":"","jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[107],"tags":[108],"class_list":["post-10459","post","type-post","status-publish","format-standard","hentry","category-excel","tag-excel"],"modified_by":"gantovnik","jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8bH0k-2IH","jetpack_likes_enabled":true,"jetpack-related-posts":[{"id":2241,"url":"https:\/\/gantovnik.com\/bio-tips\/2024\/07\/433-run-a-vba-macro-when-certain-cells-change-in-excel\/","url_meta":{"origin":10459,"position":0},"title":"#433 Run a VBA macro when certain cells change in Excel","author":"gantovnik","date":"2024-07-08","format":false,"excerpt":"When you type an entry in cells A1:C10 on Sheet1, a message box is displayed. In the module sheet behind Sheet1 add this script","rel":"","context":"In &quot;excel&quot;","block_context":{"text":"excel","link":"https:\/\/gantovnik.com\/bio-tips\/category\/excel\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2201,"url":"https:\/\/gantovnik.com\/bio-tips\/2024\/05\/428-python-script-to-open-excel-files-from-the-list-of-files-and-get-values-of-specific-cells-in-the-selected-sheet\/","url_meta":{"origin":10459,"position":1},"title":"#428 Python script to open Excel files from the list of files and get values of specific cells in the selected sheet","author":"gantovnik","date":"2024-05-26","format":false,"excerpt":"Output:","rel":"","context":"In &quot;excel&quot;","block_context":{"text":"excel","link":"https:\/\/gantovnik.com\/bio-tips\/category\/excel\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":444,"url":"https:\/\/gantovnik.com\/bio-tips\/2019\/07\/removing-duplicates\/","url_meta":{"origin":10459,"position":2},"title":"#64 Removing Duplicates","author":"gantovnik","date":"2019-07-29","format":false,"excerpt":"","rel":"","context":"In &quot;python&quot;","block_context":{"text":"python","link":"https:\/\/gantovnik.com\/bio-tips\/category\/python\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":9440,"url":"https:\/\/gantovnik.com\/bio-tips\/2025\/04\/459-python-script-to-group-consecutive-numbers-into-ranges-like-in-msc-patran\/","url_meta":{"origin":10459,"position":3},"title":"#459 Python Script to Group Consecutive Numbers into Ranges Like in MSC Patran","author":"gantovnik","date":"2025-04-20","format":false,"excerpt":"This Python script reads a text file containing a list of numbers (such as node or element IDs), sorts them, removes duplicates, and groups consecutive numbers into compact ranges - just like MSC Patran's range format. It's a handy tool for preprocessing or organizing large ID lists efficiently. def read_numbers(filename):\u2026","rel":"","context":"In &quot;patran&quot;","block_context":{"text":"patran","link":"https:\/\/gantovnik.com\/bio-tips\/category\/patran\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1969,"url":"https:\/\/gantovnik.com\/bio-tips\/2023\/09\/393-count-the-number-of-files-with-a-specific-extension-in-a-folder-using-batch-script\/","url_meta":{"origin":10459,"position":4},"title":"#393 Count the number of files with a specific extension in a folder using batch script","author":"gantovnik","date":"2023-09-23","format":false,"excerpt":"Output:","rel":"","context":"In &quot;batch&quot;","block_context":{"text":"batch","link":"https:\/\/gantovnik.com\/bio-tips\/category\/batch\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1750,"url":"https:\/\/gantovnik.com\/bio-tips\/2023\/01\/332-remove-duplicates-by-columns-and-keep-the-row-with-the-highest-value-in-the-selected-column-by-pandas\/","url_meta":{"origin":10459,"position":5},"title":"#332 Remove duplicates by columns and keep the row with the highest value in the selected column by pandas","author":"gantovnik","date":"2023-01-05","format":false,"excerpt":"table.txt ex332.py Output:","rel":"","context":"In &quot;pandas&quot;","block_context":{"text":"pandas","link":"https:\/\/gantovnik.com\/bio-tips\/category\/pandas\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/gantovnik.com\/bio-tips\/wp-json\/wp\/v2\/posts\/10459","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/gantovnik.com\/bio-tips\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/gantovnik.com\/bio-tips\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/gantovnik.com\/bio-tips\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/gantovnik.com\/bio-tips\/wp-json\/wp\/v2\/comments?post=10459"}],"version-history":[{"count":1,"href":"https:\/\/gantovnik.com\/bio-tips\/wp-json\/wp\/v2\/posts\/10459\/revisions"}],"predecessor-version":[{"id":10461,"href":"https:\/\/gantovnik.com\/bio-tips\/wp-json\/wp\/v2\/posts\/10459\/revisions\/10461"}],"wp:attachment":[{"href":"https:\/\/gantovnik.com\/bio-tips\/wp-json\/wp\/v2\/media?parent=10459"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/gantovnik.com\/bio-tips\/wp-json\/wp\/v2\/categories?post=10459"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/gantovnik.com\/bio-tips\/wp-json\/wp\/v2\/tags?post=10459"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}