Image: Diego/Adobe Stock Recently, I visited a buddy who was dealing with a printout that was undoubtedly produced by a spreadsheet application. It was a list of customer names and addresses arranged by ZIP Code, and my buddy was by hand counting the number of customers were in each ZIP Code region. I hate to see users lose time doing something manually that could be finished with software application.
SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)
Naturally, I had to stick my nose into the process and mention that there’s a far better way to get those numbers. In this tutorial, I’ll reveal you what I revealed them: How to utilize Excel’s COUNTIF() function to return the number of times a specific value– in this case, postal code– happens in a list. Along the way, you’ll likewise find out the basics of COUNTIF() so you can utilize this versatile function with your own work. Then, we’ll use SUBTOTAL() to count when filtering.
For this demonstration, I’m utilizing Microsoft 365 on a Windows 10 64-bit system, but you can likewise use this function with earlier versions of Excel. Microsoft Excel for the web supports both of the functions we’ll be dealing with here.
If you’re interested in following along more carefully, you can download our project demonstration file here.
Dive to:
COUNTIF arguments
Prior to we use either work, let’s take a look at the COUNTIF() arguments. COUNTIF() returns the number of cells that satisfy a specific condition that you define. In our case, we’re counting the variety of times a specific ZIP code happens in a defined range.
COUNTIF() uses the following syntax:
COUNTIF(variety, criteria)
where “range” determines the list of worths you are counting and “requirements” reveals the condition for counting.
Prior to we go any further, it is essential to know that COUNTIF() has one restriction. The criteria argument is restricted to 255 characters when utilizing an actual string value. It’s uncertain you’ll encounter this limitation, however if you do, you can concatenate strings using the concatenation operator & to develop a longer string.
Troubleshooting COUNTIF
If the COUNTIF() function returns absolutely nothing and you understand the values exist, think about the following actions and suggestions:
Must-read Windows coverage
- Be sure to delimit worths: For instance, “apples” will count the number of times the word apple appears in the referenced variety; if you leave out the quotation marks, it won’t work. Numeric values don’t require a delimiter, other than for dates, which use the # delimiter.
- Check the worths: Your referenced variety may have an unnecessary space character before or after other characters. Usage TRIM() to return only the worths you want.
- Inspect if your file is open: If COUNTIF() describes another workbook, that submit must be open. Otherwise, the function returns the #VALUE! error.
- Take a more detailed look at your criteria text: COUNTIF() criteria worths aren’t case delicate. However, curly quotes in criteria will return a mistake, so if you’re pasting a worth, be careful. In general, this should not be an issue.
- Do not depend on cell format: COUNTIF() can’t count cells based on fill or font color worths.
Now that you’re familiar with this function, let’s put it to utilize with a simple example.
How to use the COUNTIF function in Excel
Let’s start with a basic use of COUNTIF(). As you can see in Figure A, the function
=COUNTIF(Table1 [ZIPS],10123)
returns the worth of 2.
Figure A
Usage COUNTIF ()to count a particular item in a list. That’s because the ZIP Code value, 10123, occurs two times in the Table called Table1. If you’re not
using a Table things, use the range referral as follows:=COUNTIF( A2: A21,10123)If you’re not knowledgeable about structured referencing, Table1 [ZIPS] may puzzle you. The example data is formatted as an Excel Table things. Table1 is the Table things’s name and [ZIPS] is the column name.
Defining a single postal code is easy, however you’ll likely wish to expand on this count by consisting of all of them.
How do I count numerous items in Excel?
You can specify an actual worth when utilizing COUNTIF(), but the requirements argument supports a cell or variety reference.
To show this function’s versatility, we’ll count the variety of occurrences of each ZIP Code in the sample data. Generally, postal code will accompany other address worths such as name, address, city and state. We’re keeping our example simple on function, because those values are unimportant when you’re counting just ZIP Code values.
SEE: Microsoft 365 Provider Use Policy (TechRepublic Premium)
If you’re utilizing Microsoft 365, utilize the following expression to generate a distinct list of sorted postal code values (Figure B):
=SORT(SPECIAL(Table1 [ZIPS])
Figure B
This simple expression returns a distinct list of postal code values. SORT( )and UNIQUE()are both vibrant variety functions, readily available just in Excel 365. In our example, there’s just one expression, which remains in D2. Nevertheless, the expression spills over into the cells below to satisfy the returned values as a selection. If you get a spill error, there’s something obstructing the selection in the cells below the expression.
As soon as you have an unique list of postal code, you can utilize COUNTIF() to return the count of each ZIP Code worth, as displayed in Figure C, using
=COUNTIF(Table1 [ZIPS], D2)
and copying it to the staying cells.
Figure C
Count each ZIP Code in the distinct list. For more information about vibrant ranges, you can check out How to produce a sorted distinct list in an Excel spreadsheet. How do I count multiple items in Excel pre-365? For users who are utilizing an earlier variation of Excel than Excel 365, you’ll have to work a bit harder for the very same results. If it
is very important to you that the unique list of postal code is arranged, arrange the source information before going any even more. To do so, you can simply click on any of the cells in column A and click the Sort Ascending button in the Sort & Filter group on the Information tab. Alternatively, you can click Sort & Filter in the Modifying group on the Home tab. To produce an unique list of ZIP Codes from the worths in column A, do the following: Click any group of cells in the dataset– in this example, we have actually selected A1: A21. Click the Information tab and after that click Advanced in
- the Sort & Filter group. Click the Copy to Another Area option. Excel will display$A$ 1:
- $A$ 21 as the List Range. If it does refrain from doing this, you can fix it manually. Remove the Criteria Variety if there is one.
- Click Copy To control and after that click an unselected cell, such as G1.
- Check the Unique Records Just choice (Figure
- D). Figure D
Make certain to check the Distinct Records Just option. Click OK. This function also copies the heading text from A1 and the formatting. There’s no other way around either of these copies, but that’s okay, due to the fact that neither
- hinders
our task. At this moment, all that’s left is the function for counting unique entries in column A based on entries in the distinct list in column G. Now it’s time to get in the following function
into cell H2:=COUNTIF (Table1 [ZIPS], G2)You’ll then copy it to the remaining cells. As you can see in Figure E, this function returns the very same counts as the very first. Figure E COUNTIF ()returns the number of times each ZIP Code happens in column A. Did you see the bold 20 in
cell H9?
That’s a SUM ()function, which ensures the number of counted entries equates to the number of original entries. Because we had 20 entries in our source data in column A, we ‘d anticipate the total number of distinct entries counted to be the very same. COUNTIF( )is a practical method to count particular values in a list, but you might likewise encounter scenarios where you wish to count products in
a filtered list. Let’s cover how to do that next. How do I count filtered lists in Excel? Using COUNTIF( )works fantastic in numerous situations, however what if you desire a count based upon
the outcomes of a filtered list?
In this scenario, the COUNTIF () function won’t work for you. The function will continue to return the proper results, but it won’t return the proper count for the filtered set. Instead, you’ll want to utilize the SUBTOTAL ()function to count a filtered list. Excel’s SUBTOTAL( )function is rather special, as it accommodates filtering. Particularly, regardless of the mathematical calculation, this function examines only the values that make it to the filtered list. This function uses the following syntax: SUBTOTAL( number, recommendation)”Number “identifies the mathematical calculation and”reference “specifies the values. By default
, number is 109, which is SUM(). Describe Table A for a complete list of number values: Table A Consists of hidden rows Excludes concealed rows Function 1 101 AVERAGE 2 102 COUNT 3 103 COUNTA 4 104 MAX 5 105 MINUTES 6 106 ITEM 7 107 STDEV 8 108 STDEVP
9 109 AMOUNT
10 110 VAR 11 111 VARP In the last section, COUNTIF() didn’t care whether the source data was a normal data range or a Table item. For this solution to work, you should deal with a Table things . To convert a data range into a Table item, click anywhere inside the data variety and press Ctrl+ T and click OK to confirm the conversion. Doing so instantly shows a filter dropdown in the header cell. Before we begin filtering , we must include a special row to the Table as follows: Click anywhere inside the Table. Click the contextual Table Style tab. In the Table Design Options group, click the Total Row product (Figure F). Figure F Add an Overall row to the Table. As you can see in Figure F, this row defaults to a SUBTOTAL ()function that amounts to worths by default.
In this case, we do not want an overall but rather a count. To alter
- the SUBTOTAL()function’s argument
- , click A22 and select Count from the dropdown list
- displayed in Figure G. Figure G Modification the SUBTOTAL()function from an overall to a count. As you can see, there are several functions you can choose. Figure H reveals the count, which is 20. Figure H Choosing Count alters the result to 20 because there are 20 products in the unfiltered set. The original SUBTOTAL()
function
‘s very first argument is 109, which represents SUM(). When you change the overall function to Count, SUBTOTAL( )updates that argument to 103, which represents
COUNT( )
. Start the filtering process Once the overall row remains in place and showing the count, you’re all set to start filtering. To start, try clicking the filtering dropdown in A1 and do the following: Uncheck (Select All ). Examine the 10125 choice(Figure I). Figure I Filter for the Postal Code 10125. Click OK. As you can see in Figure J, the filtered set includes 2 items, and the SUBTOTAL - ()function now returns 2 rather of 20.
This function
is special due to the fact that, unlike other functions, SUBTOTAL()
updates when you apply a filter. Figure J SUBTOTAL()returns a count of 2 for the postal code, 10125. Let
‘s try it again
, only this time, examine two postal code (Figure K ). Figure K Filter by two postal code values. As you can see in Figure L, SUBTOTAL()returns the count of both ZIP Code values,
which is 7. SUBTOTAL ()is flexible enough to manage any filter you apply using the advanced filter feature. Figure L SUBTOTAL()returns the count of 7 for postal code values 10125 and 10124. Additional resources Whether you utilize COUNTIF() or SUBTOTAL () by means of a Table things’s total row
, counting worths is simple work
. To get more information about counting, this other TechRepublic tutorial can assist: How to use the UNIQUE ()function to return a count of distinct worths in Excel. Read next: The 8 best options to Microsoft Task(Free & Paid)(TechRepublic)Source
