{"id":4890,"date":"2025-12-09T19:06:39","date_gmt":"2025-12-09T18:06:39","guid":{"rendered":"https:\/\/sandshine.eu\/?p=4890"},"modified":"2025-12-01T19:12:19","modified_gmt":"2025-12-01T18:12:19","slug":"countifs-function-in-excel-complete-tutorial-with-examples","status":"publish","type":"post","link":"https:\/\/sandshine.eu\/index.php\/2025\/12\/09\/countifs-function-in-excel-complete-tutorial-with-examples\/","title":{"rendered":"COUNTIFS Function in Excel: Complete Tutorial with Examples"},"content":{"rendered":"\n<p>Need to count cells that meet multiple criteria? COUNTIFS tests multiple conditions simultaneously and returns how many rows match all of them. One formula handles complex counting that would otherwise require helper columns or filtering.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\">What Makes COUNTIFS Useful<\/h5>\n\n\n\n<p>COUNTIFS handles sophisticated counting tasks:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Multiple criteria<\/strong> &#8211; Test up to 127 different conditions at once<\/li>\n\n\n\n<li><strong>Different columns<\/strong> &#8211; Each condition can check a different column<\/li>\n\n\n\n<li><strong>Flexible operators<\/strong> &#8211; Use equals, greater than, less than, or text matching<\/li>\n\n\n\n<li><strong>Business intelligence<\/strong> &#8211; Count qualified leads, eligible customers, or flagged items<\/li>\n\n\n\n<li><strong>Date filtering<\/strong> &#8211; Perfect for counting records within specific time periods<\/li>\n<\/ul>\n\n\n\n<h5 class=\"wp-block-heading\">The Syntax<\/h5>\n\n\n\n<pre class=\"wp-block-code\"><code>=COUNTIFS(criteria_range1, criterion1, &#91;criteria_range2, criterion2], ...)<\/code><\/pre>\n\n\n\n<p>Pairs of ranges and criteria. All conditions must be true for a row to count.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-593e3e3\" data-block-id=\"593e3e3\"><style>.stk-593e3e3 {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 1: Regional Performance Count<\/h5>\n\n\n\n<p>Count sales over $1,000 in the West region:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Product<\/th><th>Region<\/th><th>Sales<\/th><\/tr><\/thead><tbody><tr><td>Laptop<\/td><td>West<\/td><td>$4,500<\/td><\/tr><tr><td>Mouse<\/td><td>East<\/td><td>$250<\/td><\/tr><tr><td>Keyboard<\/td><td>West<\/td><td>$800<\/td><\/tr><tr><td>Monitor<\/td><td>West<\/td><td>$1,200<\/td><\/tr><tr><td>Tablet<\/td><td>West<\/td><td>$450<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=COUNTIFS(B2:B6, \"West\", C2:C6, \"&gt;1000\")<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: 2<\/p>\n\n\n\n<p>Laptop and Monitor meet both criteria. Keyboard and Tablet are West but under $1,000.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-5ab10b0\" data-block-id=\"5ab10b0\"><style>.stk-5ab10b0 {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 2: Lead Qualification<\/h5>\n\n\n\n<p>Count leads with revenue potential over $50K AND status &#8220;Hot&#8221;:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Lead<\/th><th>Revenue Potential<\/th><th>Status<\/th><\/tr><\/thead><tbody><tr><td>ABC Corp<\/td><td>$75,000<\/td><td>Hot<\/td><\/tr><tr><td>XYZ Inc<\/td><td>$120,000<\/td><td>Warm<\/td><\/tr><tr><td>Tech Co<\/td><td>$65,000<\/td><td>Hot<\/td><\/tr><tr><td>Sales Ltd<\/td><td>$45,000<\/td><td>Hot<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=COUNTIFS(B2:B5, \"&gt;50000\", C2:C5, \"Hot\")<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: 2<\/p>\n\n\n\n<p>ABC Corp and Tech Co qualify. XYZ Inc is warm, Sales Ltd is under $50K.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-b1189d8\" data-block-id=\"b1189d8\"><style>.stk-b1189d8 {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 3: Date Range Counting<\/h5>\n\n\n\n<p>Count orders placed in January 2025:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Order<\/th><th>Date<\/th><th>Amount<\/th><\/tr><\/thead><tbody><tr><td>001<\/td><td>1\/5\/2025<\/td><td>$450<\/td><\/tr><tr><td>002<\/td><td>12\/28\/2024<\/td><td>$520<\/td><\/tr><tr><td>003<\/td><td>1\/15\/2025<\/td><td>$380<\/td><\/tr><tr><td>004<\/td><td>1\/22\/2025<\/td><td>$610<\/td><\/tr><tr><td>005<\/td><td>2\/3\/2025<\/td><td>$720<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=COUNTIFS(B2:B6, \"&gt;=1\/1\/2025\", B2:B6, \"&lt;2\/1\/2025\")<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: 3<\/p>\n\n\n\n<p>Use the same range twice with different criteria to create a date range.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-9b6b7b0\" data-block-id=\"9b6b7b0\"><style>.stk-9b6b7b0 {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 4: Inventory Management<\/h5>\n\n\n\n<p>Count products with stock below 20 AND reorder status &#8220;Yes&#8221;:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Product<\/th><th>Stock<\/th><th>Reorder Needed<\/th><\/tr><\/thead><tbody><tr><td>Item A<\/td><td>45<\/td><td>No<\/td><\/tr><tr><td>Item B<\/td><td>12<\/td><td>Yes<\/td><\/tr><tr><td>Item C<\/td><td>8<\/td><td>Yes<\/td><\/tr><tr><td>Item D<\/td><td>23<\/td><td>Yes<\/td><\/tr><tr><td>Item E<\/td><td>15<\/td><td>No<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=COUNTIFS(B2:B6, \"&lt;20\", C2:C6, \"Yes\")<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: 2<\/p>\n\n\n\n<p>Items B and C need immediate reordering. Item E is low but doesn&#8217;t need reorder. Item D needs reorder but stock is adequate.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-fac75e1\" data-block-id=\"fac75e1\"><style>.stk-fac75e1 {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 5: Performance Review Count<\/h5>\n\n\n\n<p>Count employees with sales over $100K AND rating &#8220;Excellent&#8221;:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Employee<\/th><th>Sales<\/th><th>Rating<\/th><\/tr><\/thead><tbody><tr><td>Sarah<\/td><td>$125,000<\/td><td>Excellent<\/td><\/tr><tr><td>Mike<\/td><td>$98,000<\/td><td>Good<\/td><\/tr><tr><td>Jennifer<\/td><td>$142,000<\/td><td>Excellent<\/td><\/tr><tr><td>Tom<\/td><td>$115,000<\/td><td>Good<\/td><\/tr><tr><td>Lisa<\/td><td>$135,000<\/td><td>Excellent<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=COUNTIFS(B2:B6, \"&gt;100000\", C2:C6, \"Excellent\")<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: 3<\/p>\n\n\n\n<p>Sarah, Jennifer, and Lisa meet both criteria for top performer recognition.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-c84201c\" data-block-id=\"c84201c\"><style>.stk-c84201c {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 6: Multi-Region Analysis<\/h5>\n\n\n\n<p>Count West region sales between $500 and $2,000:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Product<\/th><th>Region<\/th><th>Sales<\/th><\/tr><\/thead><tbody><tr><td>Widget A<\/td><td>West<\/td><td>$1,500<\/td><\/tr><tr><td>Widget B<\/td><td>East<\/td><td>$1,200<\/td><\/tr><tr><td>Widget C<\/td><td>West<\/td><td>$450<\/td><\/tr><tr><td>Widget D<\/td><td>West<\/td><td>$1,800<\/td><\/tr><tr><td>Widget E<\/td><td>West<\/td><td>$2,500<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=COUNTIFS(B2:B6, \"West\", C2:C6, \"&gt;=500\", C2:C6, \"&lt;=2000\")<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: 2<\/p>\n\n\n\n<p>Widgets A and D fall in the target range. Widget C is too low, Widget E is too high.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-ad6d581\" data-block-id=\"ad6d581\"><style>.stk-ad6d581 {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Common Operators<\/h5>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Operator<\/th><th>Meaning<\/th><th>Example<\/th><\/tr><\/thead><tbody><tr><td>=<\/td><td>Equal to<\/td><td>&#8220;West&#8221;<\/td><\/tr><tr><td>&gt;<\/td><td>Greater than<\/td><td>&#8220;&gt;1000&#8221;<\/td><\/tr><tr><td>&lt;<\/td><td>Less than<\/td><td>&#8220;&lt;500&#8221;<\/td><\/tr><tr><td>&gt;=<\/td><td>Greater than or equal<\/td><td>&#8220;&gt;=1\/1\/2025&#8221;<\/td><\/tr><tr><td>&lt;=<\/td><td>Less than or equal<\/td><td>&#8220;&lt;=100&#8221;<\/td><\/tr><tr><td>&lt;&gt;<\/td><td>Not equal to<\/td><td>&#8220;&lt;&gt;Pending&#8221;<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h5 class=\"wp-block-heading\">Wildcards with COUNTIFS<\/h5>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Wildcard<\/th><th>Meaning<\/th><th>Example<\/th><\/tr><\/thead><tbody><tr><td>*<\/td><td>Any number of characters<\/td><td>&#8220;Tech*&#8221; matches TechCorp, Technology<\/td><\/tr><tr><td>?<\/td><td>Single character<\/td><td>&#8220;?at&#8221; matches Cat, Bat, Hat<\/td><\/tr><tr><td>~*<\/td><td>Literal asterisk<\/td><td>&#8220;~*&#8221; matches actual * symbol<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Example<\/strong>: <code>=COUNTIFS(A2:A10, \"Sales*\", B2:B10, \"&gt;1000\")<\/code><\/p>\n\n\n\n<p>Counts rows where column A starts with &#8220;Sales&#8221; AND column B exceeds 1000.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\">Common Mistakes to Avoid<\/h5>\n\n\n\n<p><strong>Mismatched Range Sizes<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>All ranges must be the same size<\/li>\n\n\n\n<li>B2:B10 works with C2:C10<\/li>\n\n\n\n<li>B2:B10 doesn&#8217;t work with C2:C15<\/li>\n<\/ul>\n\n\n\n<p><strong>Mixing COUNTIF and COUNTIFS Syntax<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>COUNTIF: range, criteria<\/li>\n\n\n\n<li>COUNTIFS: range1, criteria1, range2, criteria2&#8230;<\/li>\n\n\n\n<li>Don&#8217;t confuse the order<\/li>\n<\/ul>\n\n\n\n<p><strong>Forgetting Quotes on Operators<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>\">1000\"<\/code> is correct<\/li>\n\n\n\n<li><code>>1000<\/code> without quotes causes an error<\/li>\n\n\n\n<li>Text and operators both need quotes<\/li>\n<\/ul>\n\n\n\n<h5 class=\"wp-block-heading\">Quick Tips<\/h5>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use the same range multiple times for between conditions<\/li>\n\n\n\n<li>Cell references don&#8217;t need quotes: <code>COUNTIFS(A:A, B1)<\/code><\/li>\n\n\n\n<li>Criteria are case-insensitive for text<\/li>\n\n\n\n<li>Empty cells are ignored in criteria evaluation<\/li>\n\n\n\n<li>You can mix exact matches with operators<\/li>\n<\/ul>\n\n\n\n<h5 class=\"wp-block-heading\">When to Use COUNTIFS<\/h5>\n\n\n\n<p>Use COUNTIFS when:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>You need to count with 2+ conditions<\/li>\n\n\n\n<li>All conditions must be true (AND logic)<\/li>\n\n\n\n<li>You&#8217;re analyzing subsets of data<\/li>\n<\/ul>\n\n\n\n<p>Don&#8217;t use COUNTIFS when:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>You only have one condition (use COUNTIF)<\/li>\n\n\n\n<li>You need OR logic (use SUMPRODUCT instead)<\/li>\n\n\n\n<li>You&#8217;re counting all non-blank cells (use COUNTA)<\/li>\n<\/ul>\n\n\n\n<h5 class=\"wp-block-heading\">Start Using It<\/h5>\n\n\n\n<p>Find data with categories or filters. Think of two conditions that matter together. Write a COUNTIFS formula testing both. Check if the result makes sense by manually verifying a few rows.<\/p>\n\n\n\n<p>COUNTIFS reveals patterns in your data instantly.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p><em>Questions about COUNTIFS? Need help with complex multi-criteria counting? Let&#8217;s connect.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>COUNTIFS counts rows meeting multiple criteria simultaneously. Test conditions across different columns and get precise counts in one formula.<\/p>\n","protected":false},"author":1,"featured_media":4891,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[35,36,37],"tags":[1270,1275,1264,1258,1266,1262,1265,1254,1274,1256,1269,1272,1267,1271,1260,1268,1263,1273,1261,1255,1043,1026,1012,1257,1259],"class_list":["post-4890","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-applications","category-excel","category-learning","tag-advanced-countifs","tag-count-with-multiple-criteria-excel","tag-countifs-date-range","tag-countifs-examples","tag-countifs-for-beginners","tag-countifs-formula-explained","tag-countifs-function-guide","tag-countifs-function-in-excel","tag-countifs-multiple-conditions","tag-countifs-multiple-criteria","tag-countifs-not-working","tag-countifs-range-error","tag-countifs-syntax","tag-countifs-tips","tag-countifs-vs-countif","tag-countifs-wildcard","tag-countifs-with-dates","tag-excel-365-countifs","tag-excel-conditional-counting","tag-excel-countifs-tutorial","tag-excel-data-analysis-functions","tag-excel-formulas-tutorial","tag-excel-tutorial-2025","tag-how-to-use-countifs","tag-learn-countifs-excel"],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":6}},"_links":{"self":[{"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/posts\/4890","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/comments?post=4890"}],"version-history":[{"count":1,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/posts\/4890\/revisions"}],"predecessor-version":[{"id":4892,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/posts\/4890\/revisions\/4892"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/media\/4891"}],"wp:attachment":[{"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/media?parent=4890"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/categories?post=4890"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/tags?post=4890"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}