How To Count Cells With a Certain Value in Excel

There are several instances when you need to count the number of cells that contain a certain value.

For example, you want to:

  • Count if a product code is duplicated or not
  • Count the existence of a text string in a given range of cells

To do this, there are several different ways in Microsoft Excel.

Method 1: Use Sum & If Functions in Excel

=SUM( IF(range=”text”, 1, 0))

Note that this is an array formula, meaning that it must be keyed in a special way. Do not simply press Enter key to finish typing this formula. You must press CTRL + SHIFT + ENTER together.

Once you key in the array formula, it looks like below ( this formula searches for the value of 5 inn the cell ranging from A1 to C3. If it finds a 5, it counts a 1. All the counts are then Summed Up.)

=SUM( IF( A1:C3 = 5, 1, 0) )

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


Method 2: Use the inbuilt Countif Function of Excel

 =COUNTIF(A1:C3, “text”)

Note that this is a simple and straight forward way. It is not considered an Array function. Just key it in, and press the Enter key.

Voila, it shows you the correct count.

There you are! Two Simple ways to count the number of occurrences of a value in a range of cells in Excel.

These above 2 methods work in all versions of Excel, on the PC and on the Mac.

Hope you will benefit from them!

Cheers,
Vinai Prakash

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!


Vinai conducts workshops to enable all Excel enthusiasts in learning simple tips, tricks, techniques in Excel. Join a 2 day workshop in Singapore, or email us the city where you want to have the workshop conducted. We can

Finding The Second Largest Value in Excel

After my Advanced Excel workshop in Singapore, one of the participants, Lai Thim had a unique problem in Excel. Her boss was always asking her to show the Top 3 products, Bottom 3 countries, or Who came in the second or Third spot in the competition. She found it quite challenging to do this in Excel, and had to resort to sorting the data, manually going to pick the top 3 or bottom 2, and then copy and paste the values in the report. 

And this manual process had to be done each week, and each month, several times. So this was a big time waster. And she is not the only one… most Excel users are not aware of the simple functions that already exist in Excel, which can do the job in just a click.

So she asked me if there is a way to find the second largest value, or the third largest value given in a data set in Excel.

Finding the Largest Value or the Smallest Value in Excel is Easy

You can easily find the highest value in any Excel data using the MAX Function, and similarly, find the smallest or minimum value by using the MIN function in Excel.

But try to find the Second highest value, or the Third Lowest in Excel, and these two MAX & MIN functions can not deliver this simple thing. They were meant for only the highest and the lowest values. This is extremely easy. Just use the Max() function. It will give you the highest value. Similarly, use the Min() function to get the smallest value.

However, to get the second largest value using this same formula, you can’t use Min() or Max() functions of Excel.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!

Using LARGE Function in Excel To Find Any Rank in Excel (including Second Highest)

Microsoft Excel has a hidden function, often unknown singe the late nineties, which can do the job perfectly. It is called LARGE.

Let’s say you have values from cell A1 to A5.

To get the Second Largest value, you could write the following formula in Excel:

=LARGE(A1:A5, 2)

This will give you the second largest value, without any fuss.

The format of the LARGE Function is =LARGE(array, k), and it picks up the Kth largest value from the array.

So to find the fifth largest country in terms of sales, you need to write the following in your Excel formula cell.

=LARGE(sales, 5)

What if you want to find the second smallest values? Well, we have a formula for that too.

Using SMALL Function To Find the Second Smallest Values in Excel

Just like Large, SMALL function will pick up the Second Smallest value from a range of cells. So you can use it for finding the Second Smallest, Third Smallest etc.

=SMALL(A1:A5, 3) would give you the third lowest value in the range of these 5 cells in Excel.

To find the second lowest values in terms of Sales, you can write

=SMALL(Sales, 2)

With this, you can find any lowest or highest, smallest or largest values in Excel easily.

Conclusion:

You can use SMALL & LARGE to pick the smallest, second smallest, largest, second largest etc. to your choice. They are pretty easy to use, and are already available in your version of Excel.

So if you use Excel 2007, or Excel 2010, Excel 2013, Excel 2016, Excel 2019 or even Microsoft Office 365, you can immediately use SMALL & LARGE Functions in Excel.

That’s it. Give it a try. This is a quick and dirty way to find such small or large values. These are not new functions… Have been there in Excel since 1995… 🙂

Have fun!

Related Posts:

  1. How To Count Cells With a Certain Value in Excel
  2. Learn the Multiple Ways to Count in Excel
  3. Simple Functions in Excel [Video]

Cheers
Vinai Prakash
Connect With Me on LinkedIn: https://www.linkedin.com/in/vinaiprakash/

Vinai loves to share new and funky ways to find information from our data, using everyday Excel techniques.

Vinai also conducts in-house and public runs of his famous Advanced Excel Techniques, and Excel Dash-boarding Techniques in Singapore, Malaysia, Indonesia, Dubai, Hong Kong, and many other countries around the globe. Connect with Vinai at Vinai@ExcelChamp.net.

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Display the File Name & File Path in Excel (PC & Mac)

Many a times, it is good to Display the location of the file in Excel.

Not just the file name, but the entire path of the file. It is a good reference to make sure you are working on the correct file, and is also good if you print out the Excel. With this option, the file path always gets printed on paper..

How to Write a Formula that will Display the File Path in Excel, automatically. 

One  useful formula that can assist us is the CELL formula. I bet you didn’t know about this in-built Excel Function…

The CELL function can help us to display the address, color, format, type or width of any cell.

The CELL Function takes a maximum of 2 arguments, and only the first one is mandatory.

=CELL(info_type, [reference])

So, if you write =CELL(“filename”) in any cell, you will get the Full Path name of this Excel file, along with the Sheet Name.

For example, I got the filename displayed as below:

D:\[email-blasting-migration.xlsx]Sheet1

According to the Excel Help, the CELL function  has the following arguments:

  • info_type Required. A text value that specifies what type of cell information you want to return. The following list shows some of the possible values of the info_type argument and the corresponding results.
    info_type Returns
    “address” Reference of the first cell in reference, as text.
    “col” Column number of the cell in reference.
    “color” The value 1 if the cell is formatted in color for negative values; otherwise returns 0 (zero).
    “contents” Value of the upper-left cell in reference; not a formula.
    “filename” Filename (including full path) of the file that contains reference, as text. Returns empty text (“”) if the worksheet that contains reference has not yet been saved.
    “format” Text value corresponding to the number format of the cell. The text values for the various formats are shown in the following table. Returns “-” at the end of the text value if the cell is formatted in color for negative values. Returns “()” at the end of the text value if the cell is formatted with parentheses for positive or all values.

Display only the File Name, without the Sheet Name

To only print the File name, you can use this long formula:

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

Keep in mind that the file should be saved somewhere. If you just open a new workbook, and try these formulas, you will get a #VALUE ERROR.  This error will disappear automatically once the file is saved, and the error will be replaced with the correct full file path name, and file name.

This formula to display the filename in Excel also works on the Mac. Any Excel for Mac version, including Excel X for Mac, to Excel 2004, 2008, 2011 for Mac also work fine in displaying the filename.

For example, for a file on Mac with the path HD:Users:local:Desktop, the formula returns
HD:Users:local:Desktop:(filename)Sheet1

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


Displaying the FilePath only

While writing macros in Excel, you may need the file’s path only, to pick up other files, or for any other reason. Just use this to get only the file’s path in Excel

=LEFT(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1),1)-1)

In this formula, we remove the Sheet Name, which starts with the [ character.

Displaying File name, File path in Excel Worksheet Header or Footer

If you wish to display the file name and/or file path in the Excel sheet’s header or footer, then it is extremely simple. Simply go to the Ribbon. Choose Insert > Header & Footer

Excel File Name, File Path

All you need to do is the click on the File Path, File Name icons.

ALternatively, if you want to type it yourself, simply type the following in the header or the footer in Excel:

&[Path]&[File]

Use &[Path] for only the File Path, and &[File] for only the File’s name.

Hope it helps you in your life with Excel.

Do let me know if you need any other functions in Excel, or if you are facing a problem in Excel. We can help you to solve everyday issues in Excel easily at ExcelChamp.Net.

And subscribe to the Excel Tips Newsletter for more such tips to improve productivity in Excel.

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!


Cheers,
Vinai Prakash

Vinai is the founder and principal trainer for Microsoft Excel Tips and Techniques seminars all over the world. To book Vinai to conduct an Excel training at your office for your company, do email to vinai@excelchamp.net.

How to Find Duplicates in Excel Quickly

With hundreds of rows in today’s spreadsheets, it is virtually impossible for anyone to spot a duplicate value, or duplicate row in Excel.

With Microsoft Excel 2007, and further enhancements in Excel 2010 and Excel 2013, it is now extremely easy to spot duplicates, and there are multiple ways to find them, or eliminate them.

1. Spot a duplicate value, and get it to change its color automatically

In this simple method, we make use of Conditional Formatting, available on the Home Tab.

  • Simply select the data range where you want to spot any duplicate values, and then click on Conditional Formatting.A drop down menu of choices appears. Select the first choice – Highlight Cell Rules. Then select the Last option -Duplicate Values.The short cut key combination is Alt +H L H D.highlight_cells_duplicate_valueSelect your favorite color to highlight the duplicate values.

    duplicate_values_popup

    Both the duplicate values are now highlighted.

    finding duplicate values in Microsoft Excel

There you are. A quick and nifty way to find the duplicate values in any column, quickly.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


We will talk about another nifty way to eliminate duplicates, once for all, in the next article.

You may find these resources useful:

Cheers,
Vinai Prakash

Vinai is passionate about maximizing Excel to improve everyday productivity. He conducts Excel workshops in Singapore, Australia, Middle East. If you would like to engage him to train your staff in maximising Excel, do contact us.

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

View All Formulas in Excel with a Single Click

When someone sends you an Excel file, you may want to find out where the formulas are, and where are the values.

But with everything showing as a number, it is difficult… and you need to move from cell to cell, to find out if it is indeed a number, or a calculation based on a formula.

Fortunately, there is a simple way to find out where the formulas are, and see them all, at a glance.

Simply press the Control Key and hold it. Then press the ` symbol. On most keyboards, it is just above the Tab Key on the Left of the keyboard.

Tilde Key to View Formulas in Excel

This is a toggle shortcut. Each time you press the keystroke., it will turn itself on or off. So press CTRL + ` and you will see all the formulas, press the same keystroke one more time, and you are back to the values.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


Give it a try… it is really fun, and cool too!

You may find these resources useful:

Cheers,
Vinai Prakash – Providing you simple tips and tricks in Excel

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Show Buttons
Hide Buttons