count assignment in excel

• COUNT Function

Counts the number of cells (or arguments) that contain a number

What is the COUNT Function?

The COUNT Function [1] is an Excel Statistical function . This function helps count the number of cells that contain a number, as well as the number of arguments that contain numbers. It will also count numbers in any given array. It was introduced in Excel in 2000.

As a financial analyst , it is useful in analyzing data if we wish to keep a count of cells in a given range.

=COUNT(value1, value2….)

Value1 (required argument) – The first item or cell reference or range for which we wish to count numbers.

Value2… (optional argument) – We can add up to 255 additional items, cell references, or ranges within which we wish to count numbers.

Remember this function will count only numbers and ignore everything else.

How to use the COUNT Function in Excel?

To understand the uses of this function, let us consider a few examples:

Let’s see the results that we get using the data below:

As seen above, the function ignored text or formula errors and counted numbers only.

The results we got in Excel are shown below:

A few observations  

• Logical values and errors are not counted by this function
• As Excel stores dates as a serial number, the function returned 1 count for date.

This function can be used for an array. If we use the formula =COUNT(B5:B10) , we will get the result 4 as shown below:

Let’s assume we imported data and wish to see the number of cells with numbers in them. The data given are shown below:

To count the cells with numeric data, we use the formula COUNT(B4:B16) .

We get 3 as the result, as shown below:

The COUNT function is fully programmed. It counts the number of cells in a range that contain numbers and returns the result as shown above.

Suppose we use the formula COUNT(B5:B17,345) . We will get the result below:

You may be wondering because B10 contains 345 in the given range. So why did the function return 4?

The reason is that in the COUNT function, all values in the formula are put side by side and then all numbers get counted. Therefore, the number “345” has nothing to do with the range. As a result, the formula will add the numbers of the two values in the formula.

Example 3 – Using COUNT function with the AVERAGE function

Suppose the prices of a certain commodity are given as below:

If we wish to find out the average price from January 8 to 12,   we can use the AVERAGE function along with the COUNT and OFFSET functions.

The formula to use will be:

The OFFSET function helped in creating dynamic rectangular ranges. By giving the starting reference B2, we specified the rows and columns the final range would include.

OFFSET will now return a range originating from the last entry in column B. Now the COUNT function is used for all of column B to get the required row offset. It counts only numeric values, so the headings, if any, are automatically ignored.

There are 12 numerical values in column B, so offset would resolve to OFFSET(B2,12,0,-5). With these values, OFFSET starts at B2, offsets 12 rows to B13, then uses -5 to extend the rectangular range up “backward” five rows to create the range B9:B12.

Finally, OFFSET returns the range B9:B12 to the AVERAGE function, which computes the average of values in that range.

Things to remember

• If we wish to count logical values, then we should use the COUNTA function.
• The function belongs to the COUNT function family. There are five variants of COUNT functions: COUNT, COUNTA, COUNTBLANK, COUNTIF, and COUNTIFS.
• We need to use the COUNTIF function or COUNTIFS function if we want to count only numbers that meet specific criteria.
• If we wish to count based on certain criteria, then we should use COUNTIF.
• The COUNT function doesn’t count logical values TRUE or FALSE.

Click here to download the sample Excel file

Additional Resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:

• Excel Functions for Finance
• Advanced Excel Formulas You Must Know
• Excel Shortcuts for PC and Mac
• COUNTIF Function
• See all Excel resources

Article Sources

• Share this article

Create a free account to unlock this Template

Access and download collection of free Templates to help power your productivity and performance.

Already have an account? Log in

Supercharge your skills with Premium Templates

Take your learning and productivity to the next level with our Premium Templates.

Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.

Already have a Self-Study or Full-Immersion membership? Log in

Access Exclusive Templates

Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.

Already have a Full-Immersion membership? Log in

How-To Geek

How to use the countif formula in microsoft excel.

Your changes have been saved

Email Is sent

Please verify your email address.

You’ve reached your account maximum for followed topics.

8 Cool Things You Can Do With Home Assistant

Proton drive is getting even better, smartthings pro looks like the ultimate smart home panel, quick links, what is the countif function, how to count the number of multiple values, limitations of the countif formula.

In Microsoft Excel,

is one of the most widely used formulas. It counts all cells in a range that matches a single condition or multiple conditions, and it's equally useful in counting cells with numbers and text in them.

allows users to count the number of cells that meet certain criteria, such as the number of times a part of a word or specific words appears on a list. In the actual formula, you'll tell Excel where it needs to look and what it needs to look for. It counts cells in a range that meets single or multiple conditions, as we'll demonstrate below.

For this tutorial, we will use simple two-column inventory chart logging school supplies and their quantities.

In an empty cell, type

  followed by an open bracket. The first argument "range" asks for the range of cells you would like to check. The second argument "criteria" asks for what exactly you want Excel to count. This is usually a text string. So, in double-quotes, add the string you want to find. Be sure to add the closing quotemark and the closing bracket.

So in our example, we want to count the number of times "Pens" appears in our inventory, which includes the range

. We'll use the following formula.

=COUNTIF(G9:G15,"Pens")

You can also count the number of times a specific number appears by putting the number in the criteria argument without quotes. Or you can use operators with numbers inside of quotes to determine results, like "<100"  to get a count of all numbers less than 100.

Related: How to Count Colored Cells in Microsoft Excel

To count the number of multiple values (e.g. the total of pens and erasers in our inventory chart), you may use the following formula.

=COUNTIF(G9:G15, "Pens")+COUNTIF(G9:G15, "Erasers")

This counts the number of erasers and pens. Note, this formula uses COUNTIF twice since there are multiple criteria being used, with one criterion per expression.

If your COUNTIF formula uses criteria matched to a string longer than 255 characters, it will return an error. To fix this, use the CONCATENATE function to match strings longer than 255 characters. You can avoid typing out the full function by simply using an ampersand (&), as demonstrated below.

=COUNTIF(A2:A5,"long string"&"another long string")

Related: How to Use the FREQUENCY Function in Excel

One behavior of COUNTIF functions to be aware of is that it disregards upper and lower case strings. Criteria that include a lower case string (e.g. "erasers") and an upper case string (e.g. "ERASERS") will match the same cells and return the same value.

Another behavior of COUNTIF functions involves the use of wildcard characters. Using an asterisk in COUNTIF criteria will match any sequence of characters. For example, =COUNTIF(A2:A5, "*eraser*") will count all cells in a range that contain the word "eraser."

When you're counting values in a range, you may be interested in highlighting the top- or bottom-ranked values .

• Microsoft Office
• Microsoft Excel

• COUNTIFS Function

Related functions 

The Excel COUNTIFS function returns the count of cells in a range that meet one or more conditions. Each condition is provided with a separate range and criteria , and all conditions must be TRUE for a cell to be included in the count. COUNTIF can be used to count cells that contain dates, numbers, and text. Criteria can include  logical operators (>,<,<>,=) and wildcards (*,?).

Purpose 

Return value .

• range1 - The first range to evaluate.
• criteria1 - The criteria to use on range1.
• range2 - [optional] The second range to evaluate.
• criteria2 - [optional] The criteria to use on range2.

How to use 

The COUNTIFS function is a versatile tool in Excel for counting cells that meet multiple criteria across different ranges. It is particularly useful when specific, conditional counts are required. For example, you can use COUNTIFS in the following scenarios:

• Sales analysis : Count the number of sales transactions that exceed a certain value within a specific region. For instance, count sales over $500 in the Western region.
• Attendance Tracking : Count the number of days an employee was absent or present in a particular month.
• Inventory Management : Tally items in an inventory that are below a certain stock level and belong to a specific category, like counting accessories with less than 10 units in stock.
• Survey Analysis : Count the number of survey responses that meet multiple criteria, such as respondents who are over a certain age and prefer a particular product or service.
• Academic Performance : Calculate the number of students who scored above a certain threshold in a specific subject, such as counting students who scored over 80% in Mathematics.

The COUNTIFS function counts the number of cells in a range that meet one or more conditions. Each condition is provided as a "pair" of arguments: range + criteria . As a result, the syntax for COUNTIFS varies according to the number of conditions needed:

If there are two conditions, there will be two range/criteria pairs. If there are three conditions, there will be three range/criteria pairs, and so on. COUNTIFS can handle up to 127 separate range/criteria pairs.

In the worksheet below, the data includes Date, Color, State, Quantity, and Total. Using the COUNTIFS function, we can count rows in the data that meet multiple conditions. Column H shows the criteria used inside COUNTIFS, and column I contains the resulting count:

Let's walk through the four COUNTIFS formulas entered in the range I5:I8.

Example #1 - Color is Red

To count cells equal to "Red" in the range C5:C16, the formula in cell I5 is:

The result is 7, since 7 cells in C5:C16 contain "Red".

Example #2 - Color is Red and State is TX

To count rows where the Color in C5:C16 is "Red" and the State in D5:D16 is "TX", we need to add another condition to test if the State is TX. We do that by adding another  range / criteria pair:

COUNTIFS returns 3 since there are three rows where the color "Red" and the State in column D is "TX". Notice that  both conditions must be TRUE to be included in the count.

Example #3 - Color is Red and Quantity is over 20

COUNTIFS can count numbers as well. To count rows where the Color is "Red" and the Amount in column F is greater than 20, the formula in cell I7 looks like this:

Notice that the criteria for Amount (">20") appears in double quotes (""), even though 20 is a number. This is one of the quirks of the COUNTIFS function and other *IFS functions: numbers that appear in criteria are enclosed in double quotes ("").

Example #4 - Color is Red, Quantity is over 20, State is TX

To add another condition to check that the State is "TX", we need to add a  third   range/ criteria pair:

This is the formula in cell I8. The meaning of this formula is: count rows where the color is "red" + qty is >20 + state is "TX".  The result is 2 since there are two rows where the color is Red, the quantity is over 20, and the state is TX. Notice that each condition requires a separate range/criteria pair and there are three pairs in total. To summarize:

• Each condition requires a separate range / criteria pair.
• All range arguments must be the same size.
• To be included in the count, all conditions must be TRUE.
• Criteria can include logical operators (>,<,<>,<=,>=) as needed.
• The COUNTIFS function is not case-sensitive.
• The order in which conditions appear does not matter. 
• Criteria are enclosed in double quotes ("").

Applying Criteria

The COUNTIFS function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Because COUNTIFS is in a group of eight functions that split logical criteria into two parts, the syntax is a bit tricky. Each condition requires a separate range and criteria , and operators need to be enclosed in double quotes (""). The table below shows some common examples:

Notice the last two examples use concatenation with the ampersand (&) character. When a criteria argument includes a value from another cell, or the result of a formula, logical operators like "<" must be joined with concatenation. This is because Excel needs to evaluate cell references and formulas first to get a value before that value can be joined to an operator.

Double quotes ("") in criteria

In general, text values need to be enclosed in double quotes, and numbers do not. However, when a logical operator is included with a number, the number and operator must be enclosed in quotes as shown below:

Note: Additional conditions must follow the same rules.

Value from another cell

When using a value from another cell in a condition, the cell reference must be  concatenated  to an operator when used. In the example below, COUNTIFS will count the values in A1:A10 that are less than the value in cell B1. Notice the less than operator (which is text) is enclosed in quotes, but ampersand (&) and cell reference are not:

Not equal to

To create "not equal to" criteria, use the "<>" operator  surrounded by double quotes (""). For example, the formula below will count cells not equal to "red" in a given range:

Blank cells

COUNTIFS can be configured to count cells that are blank or not blank in a given range as seen below:

Note: be aware that COUNTIFS treats formulas that return an empty string ("") as not blank . This can cause trouble when counting the results from other formulas. See this example for some workarounds for this problem.

The easiest way to use COUNTIFS with dates is to refer to a valid date in another cell. For example, with a valid date in cell B1, you can count dates in a given range that are greater than B1 like this:

Notice we must  concatenate  the ">" operator to the date in B1. The operator is enclosed in quotes ("") but the ampersand (&) and the cell reference are not. The safest way to  hardcode a date into COUNTIFS is to use the DATE function . This guarantees Excel will interpret the date correctly. For example, to count dates less than September 1, 2020, you can use the DATE function with COUNTIFS like this:

The wildcard characters question mark (?), asterisk(*), or tilde (~) can be used in criteria . A question mark (?) matches any one character, and an asterisk (*) matches zero or more characters of any kind. For example, to count text strings that contain the text "apple", you can use a formula like this:

To count cells that contain any 3 text characters of any kind, you can use a formula like this:

Note: wildcards only work with text values. The formula above will not count a 3-digit number like 123 or 812.

The tilde (~) is an escape character to match literal wildcards. For example, to count a literal question mark (?) in cells, you can use "~?" for criteria like this:

To count text strings that end in a question mark (?), you can add an asterisk (*) like this:

Similarly, you can count asterisks(*) with "~*", and count tildes (~) "~~".

The COUNTIFS function is designed to apply multiple criteria with AND logic. This means if you try to count cells that contain "red" or "blue" in the same range, the result will be zero (0). However, to count cells with OR logic, you can use an array constant and the SUM function like this:

The formula above will count cells in range that contain "red" or "blue". Briefly, COUNTIFS returns two counts in an array (one for "red" and one for "blue") and the SUM function returns the sum as a final result. For more information, see this example .

Limitations

The COUNTIFS function has some limitations you should be aware of:

• Conditions in COUNTIFS are joined by AND logic. In other words, all conditions must be TRUE for a cell to be included in a count. The workaround above can be used in simple situations.
• The COUNTIFS function requires actual ranges for all range arguments; you can't use an array. This means you can't alter values that appear in a range argument before applying criteria.
• COUNTIFS does not count long numbers greater than 15 digits correctly. Example here .
• COUNTIFS is not case-sensitive. To count values based on a case-sensitive condition, you can use the SUMPRODUCT function with the EXACT function .
• COUNTIFS has some other quirks, which are detailed in this article .

The most common way to work around the limitations above is to use the SUMPRODUCT function . In the current version of Excel, another option is to use the newer BYROW and BYCOL functions.

• Multiple conditions are applied with AND logic, i.e. condition 1 AND condition 2, etc.
• All ranges must be the same size or COUNTIFS will return a #VALUE! error.
• Criteria are typically enclosed in double quotes (i.e. "<100",  ">32", "TX").
• Criteria can include the wildcard characters "?" and "*" and "~".
• To match a literal question mark(?) or asterisk (*), use a tilde (~), i.e. (~?, ~*).

COUNTIFS function examples

Count non-blank cells by category

Count if two criteria match

Sum time over 30 minutes

• Customer is new

Count dates in current month

• Count cells not equal to x or y

• COUNTIFS with multiple criteria and OR logic

Count cells between dates

• COUNTIFS with variable table column

New customers per month

• Summary count by month with COUNTIFS

Count cells less than

Count not equal to multiple criteria

Course completion summary with criteria

• Highlight duplicate rows

COUNTIFS function videos

• How to use the COUNTIFS function

• How to build a simple summary table

Related functions

• COUNTIF Function

The Excel COUNTIF function returns the count of cells in a range that meet a single condition. The generic syntax is COUNTIF(range, criteria), where "range" contains the cells to count, and "criteria" is a condition that must be true for a cell to be counted. COUNTIF can be used to count cells...

The Excel COUNTIFS function returns the count of cells in a range that meet one or more conditions. Each condition is provided with a separate range and criteria , and all conditions must be TRUE for a cell to be included in the count. COUNTIF can be used to count cells that...

• COUNT Function

The Excel COUNT function returns a count of values that are numbers. Numbers include negative numbers, percentages, dates, times, fractions, and formulas that return numbers. Empty cells and text values are ignored....

• COUNTA Function

The Excel COUNTA function returns the count of cells that contain numbers, text, logical values, error values, and empty text (""). COUNTA does not count empty cells.

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Related Information

• Mark rows with logical tests
• Subtotal invoices by age
• Count items in list
• Count times in a specific range
• Excel's RACON functions
• Microsoft COUNTIFS function documentation

Get Training

Quick, clean, and to the point training.

Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.

Help us improve Exceljet

Your email address is private and not shared.

• Ablebits blog

Excel formula to count cells with certain text (exact and partial match)

The tutorial shows how to count number of cells with certain text in Excel. You will find formula examples for exact match, partial match and filtered cells.

Last week we looked at how to count cells with text in Excel , meaning all cells with any text. When analyzing large chunks of information, you may also want to know how many cells contain specific text. This tutorial explains how to do it in a simple way.

How to count cells with specific text in Excel

Microsoft Excel has a special function to conditionally count cells, the COUNTIF function . All you have to do is to supply the target text string in the criteria argument.

Here's a generic Excel formula to count number of cells containing specific text:

The following example shows it in action. Supposing, you have a list of item IDs in A2:A10 and you want to count the number of cells with a particular id, say "AA-01". Type this string in the second argument, and you will get this simple formula:

=COUNTIF(A2:A10, "AA-01")

To enable your users to count cells with any given text without the need to modify the formula, input the text in a predefined cell, say D1, and supply the cell reference:

How to count cells with certain text (partial match)

The formula discussed in the previous example matches the criteria exactly. If there is at least one different character in a cell, for instance an extra space in the end, that won't be an exact match and such a cell won't be counted.

To find the number of cells that contain certain text as part of their contents, use wildcard characters in your criteria, namely an asterisk (*) that represents any sequence or characters. Depending on your goal, a formula can look like one of the following.

Count cells that contain specific text at the very start :

Count cells that contain certain text in any position :

For example, to find how many cells in the range A2:A10 begin with "AA", use this formula:

=COUNTIF(A2:A10, "AA*")

To get the count of cells containing "AA" in any position, use this one:

=COUNTIF(A2:A10, "*AA*")

To make the formulas more dynamic, replace the hardcoded strings with cell references.

To count cells that begin with certain text:

=COUNTIF(A2:A10, D1&"*")

To count cells with certain text anywhere in them:

=COUNTIF(A2:A10, "*"&D1&"*")

Count cells that contain specific text (case-sensitive)

In situation when you need to differentiate uppercase and lowercase characters, the COUNTIF function won't work. Depending on whether you are looking for an exact or partial match, you will have to build a different formula.

Case-sensitive formula to count cells with specific text (exact match)

To count the number of cells with certain text recognizing the text case, we will use a combination of the SUMPRODUCT and EXACT functions:

How this formula works:

• EXACT compares each cell in the range against the sample text and returns an array of TRUE and FALSE values, TRUE representing exact matches and FALSE all other cells. A double hyphen (called a double unary ) coerces TRUE and FALSE into 1's and 0's.
• SUMPRODUCT sums all the elements of the array. That sum is the number of 1's, which is the number of matches.

For example, to get the number of cells in A2:A10 that contain the text in D1 and handle uppercase and lowercase as different characters, use this formula:

Case-sensitive formula to count cells with specific text (partial match)

To build a case-sensitive formula that can find a text string of interest anywhere in a cell, we are using 3 different functions:

• The case-sensitive FIND function searches for the target text in each cell of the range. If it succeeds, the function returns the position of the first character, otherwise the #VALUE! error. For the sake of clarity, we do not need to know the exact position, any number (as opposed to error) means that the cell contains the target text.
• The ISNUMBER function handles the array of numbers and errors returned by FIND and converts the numbers to TRUE and anything else to FALSE. A double unary (--) coerces the logical values into ones and zeros.
• SUMPRODUCT sums the array of 1's and 0's and returns the count of cells that contain the specified text as part of their contents.

To test the formula on real-life data, let's find how many cells in A2:A10 contain the substring input in D1:

=SUMPRODUCT(--(ISNUMBER(FIND(D1, A2:A10))))

How to count filtered cells with specific text

To count visible items in a filtered list, you will need to use a combination of 4 or more functions depending on whether you want an exact or partial match. To make the examples easier to follow, let's take a quick look at the source data first.

Formula to count filtered cells with specific text (exact match)

To count filtered cells whose contents match the sample text string exactly, use one of the following formulas:

=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A10))), --(B2:B10=F1))

=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A10, ROW(A2:A10) - MIN(ROW(A2:A10)),,1)), --(B2:B10=F1))

How these formulas work:

At the core of both formulas, you perform 2 checks:

• Identify visible and hidden rows. For this, you use the SUBTOTAL function with the function_num argument set to 103. To supply all the individual cell references to SUBTOTAL, utilize either INDIRECT (in the first formula) or a combination of OFFSET , ROW and MIN (in the second formula). Since we aim to locate visible and hidden rows, it does not really matter which column to reference (A in our example). The result of this operation is an array of 1's and 0's where ones represent visible rows and zeros - hidden rows.
• Find cells containing given text. For this, compare the sample text (F1) against the range of cells (B2:B10). The result of this operation is an array of TRUE and FALSE values, which are coerced to 1's and 0's with the help of the double unary operator.

Formula to count filtered cells with specific text (partial match)

To count filtered cells containing certain text as part of the cell contents, modify the above formulas in the following way. Instead of comparing the sample text against the range of cells, search for the target text by using ISNUMBER and FIND as explained in one of the previous examples:

=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A10))), --(ISNUMBER(FIND(F1, B2:B10))))

=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A10, ROW(A2:A10) - MIN(ROW(A2:A10)),,1)), --(ISNUMBER(FIND(F1, B2:B10))))

That's how to count the number of cells with certain text in Excel. I thank you for reading and hope to see you on our blog next week!

Available downloads

You may also be interested in.

• Excel IF wildcard statement for partial match
• Count if cell contains text, multiple strings or numbers
• Excel COUNTIFS and COUNTIF with multiple criteria
• Excel COUNTIFS with OR logic
• Excel COUNT and COUNTA functions with formula examples

Table of contents

136 comments

I want to verify if a specific set of numbers is included in a range. The specific number might be in any position in the range cells.

As an example, I tried the formula below, but then it doesn't take into count the cells that are exactly the same as search value. =IF(COUNTIF(RANGE,"*"&A1&"*"),"Y","N")) A1=123 RANGE= 123 21 ,12 12 This becomes as "N" even though one of the range cell is exactly the same as search value. But it works perfectly for the range below which there are mix of cells (some with exact value, some with the value located in a random position in the cell) and the result is "Y". 123 123, 12 21, 123 12, 21

Hi! There is no exact match between the cell value and the search value in your first example. The first cell is a number, and the formula searches for the text value "*"&A1&"*". In the second example, the second and third cell values are a partial match. Therefore, the formula returns "Y". Your formula is written incorrectly. The correct way to write it is this:

=IF(COUNTIF(RANGE,"*"&A1&"*"),"Y","N")

For more information, please visit: COUNTIF function in Excel - count if not blank, greater than, duplicate or unique

Hello, I've got a list of food additives abbreviated by the corresponding numbers and I want to count the amount of dishes for each combination.

Salad 12 Pasta 124 Meat 245

SUMPRODUCT(--EXACT(D4;$B$4:$B$500)) [D4="12"] gave me 2 hits since it counted "Salad" and "Pasta". How can I fix this?

Hello! Unfortunately, I was unable to reproduce your problem on my worksheet. Your formula and your data return a result of 1.

There was an oversight on my part. Thank you for your response - I wouldn't have found the error that fast without you!

Hi, I cannot find a solution to this problem. I have a large column with fruit and veg types in them. Some cells have just one item, other cells have multiple items separated by columns. I would like to extrapolate how many times each fruit or veg item occurs in the range. I don't want to search by specific fruit and veg type as the list is too large, but instead want a count of each unique item in the column.

Onion Potato Parsnip, Turmeric Jerusalem Artichoke Onion Jerusalem Artichoke Ginger, Jerusalem Artichoke Chive, Onion Jerusalem Artichoke, Potato Cabbage, Potato

The list should output the following:

Onion 3 Parsnip 1 Turmeric 1 Jerusalem Artichoke 4 Potato 3 Ginger 1 Chive 1 Cabbage 1

Thank you for your help!

I forgot, I would also like to be able to do the same again, but for a specific box size.

Column E2:E12 and F2:F12 look like this:

XLVB Onion XLVB Potato LVB Parsnip, Turmeric LVB Jerusalem Artichoke LVB Onion MVB Jerusalem Artichoke MVB Ginger, Jerusalem Artichoke MVB Chive, Onion SVB Jerusalem Artichoke, Potato SVB Cabbage, Potato

So, I would like to know how many Jerusalem artichokes are in the MVB, for instance. Again, without me specifying the item name.

For example,

MVB Jerusalem artichoke 2 MVB Ginger 1 MVB Chive 1 MVB Onion 1

Thanks again.

Hello! To count the occurrences of a string in a range of cells, use these instructions: How to find substring in Excel . Use the SUMPRODUCT function to summarize the number of matches.

=SUMPRODUCT(--ISNUMBER(SEARCH(E1,B1:B10)))

You can add a second condition to the formula for a specific box size:

=SUMPRODUCT((ISNUMBER(SEARCH(E1,B1:B10)) * (A1:A10=D1)))

How do I do this and not get results back that are contained in what I am searching for? i.e. I want to look across values and count the number of times "reading" appears within sentences but i do not want to count the word "read" or "ding" (etc...). The word to look for is stored in a cell.

Hi! You can create an additional column where you use a formula to write 1 if the word is found. For example:

=IF(ISNUMBER(SEARCH(D1,A1)),1,0)

For more information, please read: How to find substring in Excel To count how many times a word is found, sum the values in the column. You can also split the text into words using the TEXTSPLIT function . Then count the number of matches with the desired word using the SUM formula .

=SUM(--(TEXTSPLIT(A1,," ")=D1))

I hope it’ll be helpful. If this is not what you wanted, please describe the problem in more detail.

Thank you Alexander! but it didn't work for my use case, was getting #SPILL when trying to use a range.

For example, i have a list of strings to find:

strings to find on col D, with expected results (row by column):

D E 1 reading 2 2 writing 0 3 spelling 2

range to search (G2:G12):

reading reading ding horse w writ ting spell spelling spelling l

Hi! If you are comparing whole cell values, you can try this formula to count the number of matches:

=COUNTIF(G2:G11,D1) or =SUM(--(G2:G11=D1))

All the necessary information is in the article above.

Please, how to count values ​​from a column whose name I enter on another sheet. I want it to find the right column on the sheet according to the name I entered on another sheet and from there count the occurrence of a certain value. Thank you.

Hi! To create a dynamic reference to a cell, range, column, row, or worksheet, use the INDIRECT function. You can find the examples and detailed instructions here: Excel INDIRECT function - basic uses and formula examples . For example:

INDIRECT("'" & A1 & "'!" & B1)

Post a comment

How to Count Colored Cells in Excel (4 Simple Ways)

Counting colored cells in Excel is needed for data analysis and visualization. By counting colored cells, we can quickly identify patterns and gain insights into the dataset. This feature is handy for tracking project statuses, validating data based on color-coded criteria, creating visual summaries for reporting, aiding decision-making and facilitating a deeper understanding of the data. But there isn’t any built-in function to count the colored cells in Excel.

In this Excel tutorial, you will learn how to count colored cells in Excel.

In this dataset, there are three categories: Fruit, Flower, and Food, each marked with a different color. Fruit is in Blue, Flower is in Orange, and Food has no background color. Now, let’s count the cells with a specific color, as shown in the GIF.

Here are 4 ways to count colored cells in Excel:

Using Find & Select Command

You can use the Find & Select command to count colored cells in each category. This provides a fast and effective method for analyzing data with specific visual characteristics.

To count colored cells in Excel using the Find & Select command:

• Select the data range with colored cells.

• A Find and Replace dialogue box will pop up.
• Click Options .
• Format drop-down > Choose Format From Cell .

In the same way, you can count all the other colored cells in your worksheet in Excel.

Using SUBTOTAL Function and Filter Tool

The SUBTOTAL function counts the visible cells, and the Filter tool focuses on colored cells. It adjusts the count automatically when you modify the data or use various filters.

To count colored cells in Excel using the SUBTOTAL function and Filter tool:

• Select a blank cell below the data range.

• Select only the headers of the data range.

• Click the drop-down button in the header of the column with colored cells.
• Choose “ Filter by Color ” from the drop-down list to see all colors from your data range in a sub-list.

It will display only cells with the chosen color and show the count in the SUBTOTAL result cell.

In the same way, you can count all the other colored cells in your worksheet in Excel (e.g. when selecting the color Orange from the drop-down list, it shows the Orange-colored cells, and if there are 5 Orange cells in your dataset, the SUBTOTAL result cell will display 5).

Applying GET.CELL Macro4 and COUNTIFS Function

To count colored cells, combine the GET.CELL Macro 4 with the COUNTIFS function . GET.CELL Macro 4 gets cell color, and COUNTIFS counts cells based on different criteria, like color. The GET.CELL Macro 4 function only works on a named range in Excel.

Using Excel 4.0 Macro functions is limited because of compatibility issues and complexity. Also, it’s an older function and doesn’t have all the features of newer ones.

To count colored cells using GET.CELL Macro 4 function and COUNTIFS function:

Step 1: Create a Name Range

• Name: GetColorCode (this is a user-defined name)
• Scope: Workbook
• Refers to: =GET.CELL(38,GetCell!$B5) Here, 38 means the color of the referenced cell. GetCell means the sheet name that has your dataset. $B5 is the reference of the column with the background color.

Step 2: Find the Color Code for Each Cell

• In the adjacent to the data, write the user-defined formula: =GetColorCode

• The formula will return a specific number specified in color.

Step 3: Apply COUNTIFS Function

• Select the cell where you want to see the count of colored cells.

• Again, click on the next cell.

Read More: Count Blank Colored Cells in Excel

Using Excel VBA Code

VBA is the advancement of the Excel 4.0 macro. You can create a User-Defined Function using VBA. A user-defined function offers to use it as a regular function and is more flexible to use for new data.

To count colored cells in Excel using VBA:

• Go back to the dataset and define cells with colors as we did in the previous method.
• Write the following formula: =Count_Colored_Cells(E5,$B$5:$B$16) Here, Count_Colored_Cells is the user-defined function that you created in the VBA code. E5 is the color-defined cell and $B5:$B$16 is the range of the dataset with colored cells.

Download Practice Template

You can download the free practice Excel template from here and practice on your own.

In conclusion, this article showed you 4 ways to count colored cells in Excel: use Find & Select, try SUBTOTAL with the Filter tool for dynamic counting, or apply Excel VBA coding for more advanced customization. These ways give you flexibility, offering efficient solutions to count colored cells in Excel based on individual needs, skills, and preferences. Feel free to comment if you have any questions regarding the topic.

Frequently Asked Questions

Is excel vba coding necessary to count colored cells.

No, VBA coding is not necessary, but it offers advanced customization options. Users with basic Excel skills can effectively count colored cells using simpler methods like formulas and filters.

Can I count colored cells in a specific range?

Yes, you can count colored cells in a specific range by adjusting the range parameter in your counting formula or method. This allows you to focus on a particular subset of your data.

Are there any limitations to counting colored cells in Excel?

Count colored cells in excel: knowledge hub.

• Count Colored Cells in Excel Without VBA
• Excel Formula to Count Cells with Specific Color
• Count Cells with Specific Text and Fill/Font Color in Excel
• Count Cells by Color with Conditional Formatting in Excel
• Excel Formula to Count Colored Cells in a Row
• How to Count Colored Cells in Excel with VBA

<< Go Back to Count Cells | Formula List | Learn Excel

What is ExcelDemy?

Tags: Count Cells in Excel

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

The VBA work for me, but it does not automatically update the count when I change the color, it only updates the count when I redrag the formula back and forth. Thank you in any case, and perhaps we’ll be able to find new ways to improve this with automatic updates.

We’re glad to know that we could help you out. In the case of the VBA method, kindly refresh the worksheet after you change the color of the cells. You’ll find the Refresh button in the Data tab. However, if we can improve the code to update it automatically, we’ll let you know.

Yeah, managed to get this to work well but again, it does not auto update unless you update the cells.

We’re happy to help you out. Kindly refresh the worksheet if you change the cell colors. You’ll find the Refresh button in the Data tab. However, if we can improve the code to update it automatically, we’ll let you know.

Your steps are not numbered. Step 6 of the macro approach is not defined.

Thank you very much for correcting us. We removed the reference as it’s not really necessary. You just have to color the cells E5 and E6 in blue and orange respectively. This is for the purpose of taking reference in the argument of the function we inserted in cells F5 and F6.

Thank you again.

Leave a reply Cancel reply

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact  |  Privacy Policy  |  TOS

• User Reviews
• List of Services
• Service Pricing

• Create Basic Excel Pivot Tables
• Excel Formulas and Functions
• Excel Charts and SmartArt Graphics
• Advanced Excel Training
• Data Analysis Excel for Beginners

Advanced Excel Exercises with Solutions PDF

5 Ways to Count Characters in Microsoft Excel

Wondering how to count characters in Excel? Read this to learn all the popular ways of counting characters in Excel via real-world examples that you can put to business or personal use.

Counting characters in Excel is a fundamental skill for anyone working with spreadsheets, whether you’re a data analyst, a student, or a business professional.

Understanding how to tally the number of characters in a cell can be crucial for various tasks, such as tracking text length for social media posts, monitoring data input limits, or preparing concise reports.

In this comprehensive guide, I’ll delve into the various methods and functions available within Excel to effortlessly count characters, offering you the knowledge and tools needed to enhance your spreadsheet proficiency.

Reasons to Count Characters in Excel

Here are the reasons why you might need to utilize this skill in your business or profession:

• Preconfigured form fields and database inputs, at times, only allow you to enter content or numbers within a specific character limit. Now, if you’re uploading content to a database at millions of entries level, you can use Excel to calculate the character counts of the content and make necessary changes before uploading.
• Various social media and microblogging sites limit the number of characters you can type in a comment, message, reply, or post. If you engage with friends and family members a lot via social media, you can use the formulas mentioned in this article to count characters before submitting your posts.
• If you’re posting social media content at a business level for many clients, make your workflow efficient by counting the characters of the posts on Excel so you can reduce reworks.
• During the data cleaning process for data science and machine learning, you might discover that some features (columns) are excessively long horizontally because of the observation (rows) data. If you must standardize the size of the column length horizontally, the character counting skill of Excel will help.
• SWIFT and other data transmission systems allow only a few characters length of text. Here, you can cut down unnecessary characters by counting characters in Excel.
• Also, you can analyze meta tags and descriptions to ensure they adhere to search engine character restrictions.
• You might want to gauge content length for articles, blog posts, or product descriptions to enhance readability and SEO performance.
• This Excel skill helps you to stay within email client character limits for subject lines and preheaders to maximize open rates.
• You can ensure that your data entry agents are properly inputting data into the forms by calculating total characters and tallying that with the character count you expected.

Counting Characters in Excel Using LEN Function

The built-in function to calculate characters of text strings or numbers in Excel is the LEN function. It’s super-easy to use this function. Simply, enter the function after an equal sign and put the cell reference or text in a parenthesis.

The formula instantly tells you the total character count of the cell irrespective of the content type, like symbols, spaces, numbers, and texts.

However, this simple solution might not help you directly. You need to modify the formula in various ways. Find below some common ways to count characters in Excel using the LEN and its modified functions.

Count Characters in a Single Cell

• Enter the following formula into a cell where you want to get character counts of reference cells:
• In the formula, don’t forget to change the cell reference according to your dataset.
• Hit Enter to get the character counts of the cell content.
• Excel calculates and shows the character counts in the selected cell.

Count Characters in a Range of Cells

• Highlight a cell where you want to get a complete character count of a cell range.
• Enter the following formula into the cell’s Formula Bar :
• Modify the references (A2:A5) according to your own dataset.
• Hit Enter to get a character count of multiple cells.

Count Specific Characters

Suppose you want to know how many Zs are there in a content or text string. Do you manually count Zs in the content?

Absolutely, not! You input the text string or content into a cell in your Excel worksheet. Then, use the following formula in an adjacent cell:

The above formula expects that the content being counted is in the cell A2 and the character you’re counting is the small Z within quotes.

Creating a Case-Insensitive LEN Formula

If you want to count all small and capital letters of Z, use the following case-insensitive formula instead:

Here’s how you can modify the formula to make them work for you:

• A2 should be the reference cell that contains the content, texts, or numbers being calculated.
• The code element "z" tells Excel which alphabet or character to count. So, change this as well according to your needs.

Counting a Substring in a Cell

Sometimes, you need to count a specific substring in a long text, code, or content. You can do that using the following steps:

• Highlight the cell where you want to get the total count of a substring in a long text or number string.
• Enter the following formula into the cell:
• Hit Enter to get the character count instantly.

You can convert the above formula to take relative values from cell references for ease of work. Here’s a modified version of the same formula:

Here’s how to customize this formula:

• Enter the content cell or cell range reference in place of A2 .
• Similarly, replace B2 with your own cell reference or range for the substring to be counted.

Count Characters After and Before a Decimal

Suppose, the content in a cell has a decimal as the delimiter. Now, you want to count how many characters are there before and after the decimal place.

In this situation, you can use the following Excel formula and steps:

• Highlight a cell and enter this formula into it:
• Hit Enter to know how many numericals are there before the decimal point.

Now, to calculate the digits after the decimal point, use this modified LEN formula:

In both the above formulas, A2 is the cell reference for the content being counted. So, modify the formula accordingly when using it in your own worksheet.

Count Characters in Excel in Power Query

So far, you’ve gone through various LEN functions to count characters in Excel. If you need to scale up the process for millions of entries, the LEN function isn’t an efficient choice.

You must use Power Query! Also, if you’re importing the strings or content for character counting from an external database, you’d use Power Query for the data import.

While importing, you can transform your data in Power Query, extract character counts, and add that to the worksheet directly. Find below the Power Query formulas and steps you must try.

There are two ways to add a dataset to Power Query. The first one is using third-party data sources. Here’s how:

• Click the Data tab on the Excel ribbon menu.
• Go to the Get & Transform Data commands section and click Get Data .
• Hover the cursor over the database platform like From Azure .
• Select the data container on the overflow menu, like From Azure SQL Database .
• Power Query will open and you should find tabulated data inside the tool.

Another way to inject data into Power Query is from your existing Excel worksheet. Here’s how to do that:

• Highlight the dataset you want to import to Power Query.
• Click the Data tab.
• Click From Table/Range on the Get & Transform Data section.

• Click OK on the Create Table dialog box.
• The dataset will open on the Power Query Editor .

After importing an external or local worksheet dataset to Power Query, you must apply a formula to count the characters in the input dataset. Here’s how it’s done:

• Go to the Power Query Editor window.
• Click the Add Column tab.
• Click Custom Column inside the General commands block.
• On the Custom Column dialog box, enter a name for the new column.
• Inside the Custom column formula field, enter the following formula:
• Click OK to create the new column with a formula.

• The new column should show the character counts.
• Click the File tab and choose the Close & Load To option.

• On the Import Data dialog, select Existing worksheet .
• Also, highlight the area where you want the data from Power Query to be imported.
• Click OK to complete the import process.

The above image shows the way Excel imports data to the existing worksheet. You can now change the table style to reformat your dataset.

Counting Characters in Excel in Power Pivot Measure

Find below the steps for counting characters in Excel using Power Pivot Measure:

• Highlight the source dataset and click the Power Pivot tab.
• Click the Add to Data Model button.
• Click OK on the Create Table dialog.
• You should now see the dataset inside the Power Pivot tool.
• Click the first row below the first column where you see your data.
• There, enter the following formula:
• Hit Enter and Excel will create a new measure for you.

Now, find below how to use the Power Pivot Measure in a PivotTable in Excel:

• Click the PivotTable button on the Power Query ribbon.

• On the Create PivotTable dialog, select Existing Worksheet and click OK .

• You should now see the PivotTable .
• Expand the table you started with, Table6 in this tutorial, on the PivotTable Field panel.
• Checkmark Measure 5 .
• You should see the sum of characters in the selected cell ranges.

Count Characters in Excel Using VBA

Excel VBA enables you to fully automate the character counting process in Excel. Find below a script that will count the characters in a cell range A2:A10 and put the results in B2:B10 :

Here’s how you can use this script on your worksheet:

• Press Alt + F11 to bring up the Excel VBA Editor .
• On the menubar, you shall find the Insert menu. Click on it.
• A context menu will show up with a few options. Click on Module .
• On the new module, copy and paste the above script.
• Sheet2 should be replaced with the actual worksheet name
• A2:A10 should be replaced with the input data source on your sheet
• B2:B10 also needs to be changed if you want the character counts in another cell range
• Click the Save button to save the script.
• Click Close to close the Excel VBA Editor .

So far, you’ve created a VBA Macro named CountCharacters . Here’s how to run it on your worksheet:

• Press Alt + F8 to call the Macro dialog box .
• Select the macro you want to run.
• Hit the Run button.

Excel should count the characters of the source cell range and populate the results in the destination cell range.

Find below another Excel VBA script that will count a specific character in the given cell or cell range containing strings, numbers, or special characters:

Find the dataset structure and results in the given image. You can customize the script by following the comments in it.

Counting Characters in Excel Using Office Scripts

Since Office Scripts works on both the Excel desktop app and Excel on the Web, you can also try this tool to count characters. Also, you can further automate tasks on Excel-like emailing to a client using Power Automate and Office Scripts.

Find below the script that counts specific characters in a range of cells and populates the results in adjacent columns:

To use this script, follow these steps:

• Click the Automate tab.
• Click New Script .
• Copy and paste the above script into the Code Editor interface.
• Click Run .

Conclusions

Mastering the art of character counting in Excel is an invaluable skill for a multitude of tasks, from data validation to content optimization.

With various techniques at your fingertips, such as Excel LEN function, Power Query , Power Pivot Measure, VBA scripts , and Office Scripts , you’re equipped to efficiently handle character-related challenges in your spreadsheets.

About the Author

Subscribe for awesome Microsoft Excel videos 😃

• Pivot Table Tips and Tricks You Need to Know
• Everything You Need to Know About Excel Tables
• The Complete Guide to Power Query
• Introduction To Power Query M Code
• The Complete List of Keyboard Shortcuts in Microsoft Excel
• The Complete List of VBA Keyboard Shortcuts in Microsoft Excel

Related Posts

8 Ways to Unhide Sheets in Microsoft Excel

Jun 12, 2024

This effortless Microsoft Excel tutorial will help you learn how to unhide...

Where Is the Status Bar in Microsoft Excel?

Are you wondering where is the status bar in Excel? Read this ultimate Excel...

9 Best Ways To Take a Screenshot in Microsoft Excel

Jun 9, 2024

I'll show you how to take a screenshot in Excel step by step in this...

Submit a Comment Cancel reply

Your email address will not be published. Required fields are marked *

Save my name, email, and website in this browser for the next time I comment.

Submit Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed .

Get the Latest Microsoft Excel Tips

Follow us to stay up to date with the latest in Microsoft Excel!

• Overview of formulas in Excel Article
• XLOOKUP Article
• VLOOKUP Article
• SUM function Article
• COUNTIF function Article
• IF function Article
• IFS Article
• SUMIF Article
• SUMIFS Article
• MATCH Article

XLOOKUP function

Use the XLOOKUP function to find things in a table or range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. With XLOOKUP, you can look in one column for a search term and return a result from the same row in another column, regardless of which side the return column is on.

Note:  XLOOKUP is not available in Excel 2016 and Excel 2019, however, you may come across a situation of using a workbook in Excel 2016 or Excel 2019 with the XLOOKUP function in it created by someone else using a newer version of Excel.

The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match. 

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

Example 1     uses XLOOKUP to look up a country name in a range, and then return its telephone country code. It includes the lookup_value (cell F2), lookup_array (range B2:B11), and return_array (range D2:D11) arguments. It doesn't include the match_mode argument, as XLOOKUP produces an exact match by default.

Note:  XLOOKUP uses a lookup array and a return array, whereas VLOOKUP uses a single table array followed by a column index number. The equivalent VLOOKUP formula in this case would be: =VLOOKUP(F2,B2:D11,3,FALSE)

———————————————————————————

Example 2     looks up employee information based on an employee ID number. Unlike VLOOKUP, XLOOKUP can return an array with multiple items, so a single formula can return both employee name and department from cells C5:D14.

Example 3     adds an  if_not_found argument to the preceding example.

Example 4     looks in column C for the personal income entered in cell E2, and finds a matching tax rate in column B. It sets the if_not_found argument to return  0 (zero) if nothing is found. The match_mode argument is set to 1 , which means the function will look for an exact match, and if it can't find one, it returns the next larger item. Finally, the search_mode argument is set to 1 , which means the function will search from the first item to the last.

Note:  XARRAY's lookup_array column is to the right of the return_array column, whereas VLOOKUP can only look from left-to-right.

Example 5     uses a nested XLOOKUP function to perform both a vertical and horizontal match. It first looks for Gross Profit in column B, then looks for Qtr1 in the top row of the table (range C5:F5), and finally returns the value at the intersection of the two. This is similar to using the INDEX and MATCH functions together.

Tip:  You can also use XLOOKUP to replace the HLOOKUP function.

Note:  The formula in cells D3:F3 is: =XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17)) .

Example 6     uses the SUM function , and two nested XLOOKUP functions, to sum all the values between two ranges. In this case, we want to sum the values for grapes, bananas, and include pears, which are between the two.

The formula in cell E3 is:  =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))

How does it work? XLOOKUP returns a range, so when it calculates, the formula ends up looking like this: =SUM($E$7:$E$9) . You can see how this works on your own by selecting a cell with an XLOOKUP formula similar to this one, then select  Formulas > Formula Auditing > Evaluate Formula , and then select Evaluate  to step through the calculation.

Note:  Thanks to Microsoft Excel MVP, Bill Jelen , for suggesting this example.

You can always ask an expert in the Excel Tech Community  or get support in  Communities .

XMATCH function

Excel functions (alphabetical)

Excel functions (by category)

Need more help?

Want more options.

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.