[et_pb_section fb_built=”1″ module_class=”sheetgo-post” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_row _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
A common task in Excel often involves taking a set of data and tallying the number of occurrences that match certain characteristics. The COUNTIF Excel function<\/a> allows you to do just that: it counts the number of data points (cells) that meet a certain criteria that you specify within the function.<\/p>\n Because of the wide range of criteria that you can input, the COUNTIF function allows you to filter your data in virtually any way you want.<\/p>\n This guide shows how to use COUNTIF in Excel with text, numbers, cell references, operators, and wildcards, with step-by-step examples. It also covers COUNTIFS, which counts cells that meet several conditions at once, and the errors you’re most likely to run into.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Both functions count the cells that meet a condition. The difference is how many conditions they check.<\/p>\n Use COUNTIF for a single condition and COUNTIFS when a row has to meet two or more conditions at once. To count rows that meet one condition or another, such as people aged 20 or 21, add two COUNTIF results together, as in the number example below. COUNTIFS has been in Excel since Excel 2007.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=COUNTIF(range, criteria)[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n To illustrate some of the ways that you can use the COUNTIF function in Excel, take a look at the dataset below. It outlines the dietary preferences of a group of people. In the first example, I use COUNTIF to tally the number of vegetarians in the group by typing =COUNTIF(D2:D9,”Yes”)<\/b>.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/1.-Countif-text-string.png” alt=”Countif Excel \u2014 COUNTIF on the Vegetarian column with Yes as the criterion returns 4″ title_text=”COUNTIF on the Vegetarian column with Yes as the criterion returns 4″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In this example, the criterion that I\u2019ve specified is a text string. The function looks at all the cells in the range that I\u2019ve indicated, and returns the number of cells that match the criteria \u2013 in this case, the number of instances where “Yes” appears in the column.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Another way of indicating the criteria is to use a cell reference. Take a look at the example below. Here, I count the number of people with allergies by using cell E4 as my criteria. Of course, that cell contains the text string Yes, so this is just an alternative to typing \u201cYes\u201d into the formula.<\/p>\n The formula I use here is =COUNTIF(E2:E9,E4)<\/b>.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/2.-Countif-cell-ref.png” alt=”Countif Excel \u2014 COUNTIF with cell E4 as the criterion counts 4 people with allergies” title_text=”COUNTIF with cell E4 as the criterion counts 4 people with allergies” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You can also put a number as your criteria in the COUNTIF function. In this example, I want to count only people of a certain age, so I put the age directly into the function.<\/p>\n Notice that unlike a text string,\u00a0 you don\u2019t need to put quotation marks around a number in a formula.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/3.-Countif-number.png” alt=”Countif Excel \u2014 two COUNTIF functions added together count 5 people aged 20 and 21″ title_text=”Two COUNTIF functions added together count 5 people aged 20 and 21″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Another thing I’ve done differently here is: I added two COUNTIF functions together. The reason for this is that I want to count all the people aged 20 and 21.<\/p>\n In order to do this, I use two separate COUNTIF functions. This may sound cumbersome, but the good news is that because COUNTIF just gives a final tally of cells that meet the criteria, you can use multiple COUNTIF functions in the same formula. You simply add them together to get the total count of all cells that match multiple criteria. You can also subtract COUNTIF functions when you want to get cells that are between two criteria.<\/p>\n Adding COUNTIF results like this gives you an OR count: people aged 20 or 21. When every condition has to be true at once, such as people aged 20 who are also vegetarian, use the COUNTIFS function<\/a>, covered further down this guide.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Logical operators such as > and < (greater than & less than) can be used within your criteria.<\/p>\n In the example below, I use the < symbol to count all the people younger than 23 in my dataset, by typing =COUNTIF(B2:B9,”<23″)<\/b>.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/4.-Countif-logical-operator.png” alt=”Countif Excel \u2014 COUNTIF with a less than 23 criterion counts 7 people” title_text=”COUNTIF with a less than 23 criterion counts 7 people” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Note that this criteria includes both the logical operator symbol and a number. The criteria must be enclosed in quotation marks, unlike when you use a number only.<\/p>\n The following logical operators can be used in the same way:<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/5.-Logical-Operators.png” alt=”Countif Excel \u2014 table of the six logical operators and what each one means” title_text=”Table of the six logical operators and what each one means” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You\u2019ve now seen the criteria part of the function as a text string, a number, and a cell reference.<\/p>\n Let me show you how to do a combination of these by concatenating (combining) different criteria using the ampersand (&). I do this below by typing =COUNTIF(B2:B9,”<“&B7)<\/b>.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/6.-Countif-Concatenate.png” alt=”Countif Excel \u2014 the less than sign joined to cell B7 with an ampersand also returns 7″ title_text=”The less than sign joined to cell B7 with an ampersand also returns 7″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The result of this function is exactly the same as the previous example.<\/p>\n Except, I have now used the & symbol to concatenate a cell reference (B7) which contains the number 23, instead of simply typing the number 23 into the formula.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n On top of the different ways to set the criteria in the COUNTIF function, there is something else called “wildcards”.<\/p>\n The wildcard characters * and ? can be used within your criteria to indicate a match with any character (?) or any sequence of characters (*).<\/p>\n The example below has very specific criteria. I want to count all the occurrences of a 5-letter name that ends in \u201cy\u201d. To do this, you can use the ? wildcard.<\/p>\n This wildcard character is a stand-in for any character \u2013 so when I type =COUNTIF(A2:A9,”????y”)<\/b> I indicate that I want to count all instances where there are four characters (which can be anything) followed by a \u201cy\u201d.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/7.-Countif-name-example.png” alt=”Countif Excel \u2014 the ????y wildcard counts 2 five-letter names ending in y” title_text=”The ????y wildcard counts 2 five-letter names ending in y” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Use the second wildcard (*) when the length of the criteria doesn\u2019t matter \u2013 for instance, if I didn\u2019t care about the length of the name, just that it ended in \u201cy\u201d, the criteria could be \u201c*y\u201d.<\/p>\n Another handy use for the * wildcard is to get rid of blank cells. I demonstrate that in the screenshot below using the formula =COUNTIF(A2:A13,”*”)<\/b>.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/8.-Countif-blank-cells.png” alt=”Countif Excel \u2014 the * wildcard counts 8 non-blank cells in A2:A13″ title_text=”The * wildcard counts 8 non-blank cells in A2:A13″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Because the * represents any number of any characters, the only cells that don\u2019t meet this criteria are the ones that don\u2019t contain anything at all!<\/p>\n [\/et_pb_text][et_pb_text module_id=”countif-criteria-and-operators-in-excel” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Below, you have some of the most useful operators you can use in your COUNTIF criteria, whether you need to count based on text, numbers, blanks & non-blanks, or Boolean values.<\/p>\n When it comes to matching text values, you have a lot of options. For an exact match, just enclose the string in quotation marks or the reference to the cell containing the string. For partial matches, you can use the wildcard characters below.<\/p>\n Finally, the function is case insensitive, so it won\u2019t distinguish between uppercase and lowercase.<\/p>\n If you want to use an exact numeric value as the criterion, you don\u2019t need to enclose it in quotation marks. However, if you want to use comparison operators, you need to enclose the criterion in quotation marks.<\/p>\n Dates work the same way, because Excel stores them as numbers. For example, =COUNTIF(B2:B100,">="&DATE(2026,1,1)) counts the dates on or after January 1, 2026.<\/p>\n Use these criteria to count blank and non-blank cells.<\/p>\n The * wildcard from the wildcards example also skips blank cells, but it only counts cells that hold text, so it leaves numbers out.<\/p>\n Finally, you can also use the Boolean operators TRUE and FALSE to count cells based on the results of a formula or to count checked or unchecked checkboxes. For example, =COUNTIF(E2:E20,TRUE) counts the checked boxes in E2:E20.<\/p>\n [\/et_pb_text][et_pb_text module_id=”countif-examples-in-excel-step-by-step” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Now that you have reviewed the syntax for the COUNTIF function, it\u2019s time to see some examples of how you can use it in different situations.<\/p>\n Imagine you have an Excel table with sales data, which includes the name of the sales agent, their team, the product, and the sales amount. You need to get different counts for your sales report, but counting manually would be a nightmare, as the table has five hundred rows.<\/p>\n First, select each column and go to the \u2018Formulas\u2019 tab. Click \u2018Create from Selection\u2019 to name the ranges so it’s easier to work with the data.<\/p>\n The screenshots come from Excel set to European regional settings, so they show semicolons between arguments. In the US and UK versions of Excel, you type commas, as in the steps below.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/countif-excel-name-manager-named-ranges.webp” alt=”COUNTIF in Excel \u2014 Name Manager listing the named ranges product, sales_agent, sales_amount, sales_data and sales_team” title_text=”Name Manager listing the named ranges product, sales_agent, sales_amount, sales_data and sales_team” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Follow the steps below to count cells that contain specific text. In this case, let’s count the number of sales by \u2018Barret Smitten\u2019.<\/p>\n 1.<\/strong> Type the COUNTIF function and select the range of cells you want to count. In this case, I will use the named range \u2018sales_agent\u2019.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/countif-excel-exact-text-range.webp” alt=”COUNTIF in Excel \u2014 COUNTIF typed in F2 with the sales_agent range selected” title_text=”COUNTIF typed in F2 with the sales_agent range selected” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 2.<\/strong> Add a comma and type the criterion between quotation marks.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/countif-excel-exact-text-criterion.webp” alt=”COUNTIF in Excel \u2014 Barret Smitten typed in quotation marks as the criterion” title_text=”Barret Smitten typed in quotation marks as the criterion” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 3.<\/strong> Add the closing parenthesis and press \u2018Enter\u2019 to see the result.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/countif-excel-exact-text-result.webp” alt=”COUNTIF in Excel \u2014 21 sales by Barret Smitten in F2″ title_text=”21 sales by Barret Smitten in F2″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The finished formula is =COUNTIF(sales_agent,”Barret Smitten”)<\/b>, and it returns 21.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Instead of using an exact match, you can use wildcards to search for part of the text. For example, let\u2019s count the number of sales made by \u2018Northteam\u2019 by matching \u2018North\u2019.<\/p>\n 1.<\/strong> Type the COUNTIF function and select the range of cells you want to count.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/countif-excel-partial-text-range.webp” alt=”COUNTIF in Excel \u2014 COUNTIF typed in F5 with the sales_team range selected” title_text=”COUNTIF typed in F5 with the sales_team range selected” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 2.<\/strong> Add a comma and type the criterion between quotation marks.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/countif-excel-partial-text-criterion.webp” alt=”COUNTIF in Excel \u2014 North* wildcard criterion in quotation marks” title_text=”North* wildcard criterion in quotation marks” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 3.<\/strong> Add the closing parenthesis and press \u2018Enter\u2019 to see the result.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/countif-excel-partial-text-result.webp” alt=”COUNTIF in Excel \u2014 189 sales by Northteam in F5″ title_text=”189 sales by Northteam in F5″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The finished formula is =COUNTIF(sales_team,”North*”)<\/b>, and it returns 189.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n For this example, let\u2019s find the number of sales for an amount greater than or equal to $500.<\/p>\n 1.<\/strong> Type the COUNTIF function and select the range of cells you want to count.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/countif-excel-greater-than-range.webp” alt=”COUNTIF in Excel \u2014 COUNTIF typed in F8 with the sales_amount range selected” title_text=”COUNTIF typed in F8 with the sales_amount range selected” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 2.<\/strong> Add a comma and type the criterion between quotation marks.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/countif-excel-greater-than-criterion.webp” alt=”COUNTIF in Excel \u2014 greater than or equal to 500 typed as the criterion” title_text=”Greater than or equal to 500 typed as the criterion” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 3.<\/strong> Add the closing parenthesis and press \u2018Enter\u2019 to see the result.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/countif-excel-greater-than-result.webp” alt=”COUNTIF in Excel \u2014 412 sales of $500 or more in F8″ title_text=”412 sales of $500 or more in F8″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The finished formula is =COUNTIF(sales_amount,”>=500″)<\/b>, and it returns 412.<\/p>\n [\/et_pb_text][et_pb_text module_id=”how-to-use-countifs-in-excel-with-multip” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In Microsoft Excel, you can use the COUNTIFS function to count cells based on multiple criteria, and each criterion can be applied to one or more ranges.<\/p>\n =COUNTIFS(criteria_range1, criterion1, criteria_range2, criterion2, …)<\/b><\/p>\n Like COUNTIF, the COUNTIFS function has two required parameters. However, you can provide additional parameters to test multiple ranges using different criteria. Remember that all ranges must have the same dimensions, meaning the same number of rows and columns.<\/p>\n The examples below use the same sales table as the COUNTIF examples.<\/p>\n For example, let’s count the sales above $600 and below $1,000. The label in the screenshots says $500, but the formula and its result use $600.<\/p>\n 1.<\/strong> Type the COUNTIFS function and select the range of cells you want to count.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/countifs-excel-one-range-first-range.webp” alt=”COUNTIFS in Excel \u2014 COUNTIFS typed in F2 with the sales_amount range selected” title_text=”COUNTIFS typed in F2 with the sales_amount range selected” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 2.<\/strong> Add a comma and type the first criterion between quotation marks.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/countifs-excel-one-range-first-criterion.webp” alt=”COUNTIFS in Excel \u2014 greater than 600 typed as the first criterion” title_text=”Greater than 600 typed as the first criterion” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 3.<\/strong> Add a comma and select the same range as before. Add another comma and type the second criterion between quotation marks.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/countifs-excel-one-range-second-criterion.webp” alt=”COUNTIFS in Excel \u2014 sales_amount selected again with less than 1000 as the second criterion” title_text=”Sales_amount selected again with less than 1000 as the second criterion” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 4.<\/strong> Add the closing parenthesis and press \u2018Enter\u2019 to see the result.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/countifs-excel-one-range-result.webp” alt=”COUNTIFS in Excel \u2014 103 sales above $600 and below $1,000 in F2″ title_text=”103 sales above $600 and below $1,000 in F2″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The finished formula is =COUNTIFS(sales_amount,”>600″,sales_amount,”<1000″)<\/b>, and it returns 103.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Finally, let\u2019s count the number of sales of productA for an amount greater than or equal to $700.<\/p>\n 1.<\/strong> Type the COUNTIFS function and select the first range of cells.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/countifs-excel-two-ranges-first-range.webp” alt=”COUNTIFS in Excel \u2014 COUNTIFS typed in F5 with the product range selected” title_text=”COUNTIFS typed in F5 with the product range selected” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 2.<\/strong> Add a comma and type the first criterion between quotation marks.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/countifs-excel-two-ranges-first-criterion.webp” alt=”COUNTIFS in Excel \u2014 productA typed in quotation marks as the first criterion” title_text=”ProductA typed in quotation marks as the first criterion” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 3.<\/strong> Add a comma and select the second range of cells. Add another comma and type the second criterion between quotation marks.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/countifs-excel-two-ranges-second-criterion.webp” alt=”COUNTIFS in Excel \u2014 sales_amount range with greater than or equal to 700 as the second criterion” title_text=”Sales_amount range with greater than or equal to 700 as the second criterion” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 4.<\/strong> Add the closing parenthesis and press \u2018Enter\u2019 to see the result.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/countifs-excel-two-ranges-result.webp” alt=”COUNTIFS in Excel \u2014 80 productA sales of $700 or more in F5″ title_text=”80 productA sales of $700 or more in F5″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The finished formula is =COUNTIFS(product,”productA”,sales_amount,”>=700″)<\/b>, and it returns 80.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n By now, you hopefully have a good grasp on the many ways the Excel COUNTIF and COUNTIFS functions can be used, and can put them into practice with your own data. Happy counting!<\/p>\n To add up values instead of counting them, see how to use SUMIF in Excel<\/a>. To find a value in a table, read our guide to VLOOKUP in Excel<\/a>, and for more formulas worth knowing, see the top 10 Excel functions<\/a>.<\/p>\n If you work in Google Sheets, see how to use COUNTIFS in Google Sheets<\/a> and our blog post on the DCOUNT formula in Google Sheets<\/a>.<\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" A common task in Excel often involves taking a set of data and tallying the number of occurrences that match certain characteristics. The COUNTIF Excel function allows you to do just that: it counts the number of data points (cells) that meet a certain criteria that you specify within the function. Because of the wide […]<\/p>\n","protected":false},"author":40,"featured_media":30746,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[65],"tags":[39,48,28],"class_list":["post-30744","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-functions","tag-connections-t","tag-excel","tag-spreadsheets"],"acf":[],"permalink_manager":null,"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/30744","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/comments?post=30744"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/30744\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media\/30746"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media?parent=30744"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/categories?post=30744"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/tags?post=30744"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}COUNTIF vs COUNTIFS: which one do you need?<\/h2>\n
\n
Syntax<\/h2>\n
\n
How to use COUNTIF in Excel<\/h2>\n
Criteria as text string<\/h3>\n
Criteria as cell reference<\/h3>\n
Criteria as a number<\/h3>\n
Criteria with logical operator<\/h3>\n
Concatenated criteria<\/h3>\n
Wildcards<\/h3>\n
COUNTIF criteria and operators in Excel<\/h2>\n
Text values<\/h3>\n
\n
Numbers and dates<\/h3>\n
\n
Blank and non-blank cells<\/h3>\n
\n
TRUE and FALSE values<\/h3>\n
COUNTIF examples in Excel, step by step<\/h2>\n
Count cells that contain exact text<\/h3>\n
Count cells that contain partial text<\/h3>\n
Count values greater than or equal to a number<\/h3>\n
How to use COUNTIFS in Excel with multiple criteria<\/h2>\n
COUNTIFS syntax<\/h3>\n
\n
COUNTIFS with two criteria on one range<\/h3>\n
COUNTIFS with criteria on different ranges<\/h3>\n
Common COUNTIF and COUNTIFS errors and how to fix them<\/h2>\n
\n
That’s how to use COUNTIF and COUNTIFS in Excel<\/h2>\n