Using the COUNTIF function in Excel: Beginner to Advanced

The COUNTIF Formula is used to count the number of cells that match a specific condition. It's hands down a game changer of a formula because at it's very basic it can give you some basic stats on data but as you'll see when we delve deeper that as the level of sophistication improves so does it's power at analysing.

But...let's start at the basics, the Formula itself is entered as

=COUNTIF(range, condition)

and is found under the FORMULAS tab in the Ribbon. Click More Functions, select Statistical and scroll down to COUNTIF.

The range entered will be the area in which Excel will search for the condition and may be manually entered or entered by highlighting the area within the worksheet that contains the data.

Conditions set by the user could be an exact wording match, counting the cells that match an individual cell, could be multiple conditions, or it could be a greater than, less than, or in between condition. The COUNTIF Function is useful to quickly count the number of customers in a geographic region or find how many types of inventory one has or even find how many products are currently being sold at a price between $1.00 and $5.00. 

Consider the following worksheet as we delve into the various conditions used within the COUNTIF Function.

Counting Cells with an exact wording match
​
If you wanted to know how many Projects are Renovation Projects you could easily scan hundreds of projects at once by entering the formula into any unused cell, typing 

=COUNTIF(B2:B9,”Renovation”)

Keep in mind that conditions are not case sensitive. Entering in “renovation” will yield the same result.

The range for this example could be the entire worksheet or in this case we are searching down the second column to count how many times “Renovation” is entered and the range could be narrowed down to just that column. The result in this instance would be 4. This condition works successfully if all data is accurate and consistent.

Counting Cells that Match

If you wanted to know how many Projects are located in New York City you could use the same formula as above and type in “New York City” as the condition or you can click on a Cell that contains the word “New York City” which in this case is A2 and Excel will match all other cells and total the findings. This formula would be entered into any unused cell as =COUNTIF(A2:C9),A2) hit enter and the result will yield a 2. 

Counting Cells using a Greater Than or Less Than Formula

To find the number of Projects that cost more than $10,000 use the formula,

=COUNTIF(A2:C9,”>10000”)

Be sure to enter the quotes before and after the condition and then close out the parenthesis. The result will be 5 in this case. Similarly, if the desired result is for all items priced equal to or greater than $10000 the condition would be entered as “>=10000” resulting in a value of 6.

The less than formula is the same as the greater than formula except the condition would be entered as “<10000” or for less than or equal to “<=10000”.

This formula could also be written with a match cells formula instead of the exact wording. =COUNTIF(A2:C9,”>”&C5)

The operator (>) must be enclosed in quotes and immediately followed by the ampersand sign then the cell that contains the data, in this case C5.

Counting Cells in between two values

Should you want to locate the total Projects that have a price range of $10,000 to $20,000 the formula would be:​

=COUNTIF(A2:C9,">=10000") - COUNTIF(A2:C9,">20000")

that is excel is searching for all items equal to or greater than $10,000 and subtracting all items greater than $20,000 to show only those items with prices ranging from ten to twenty thousand.

Counting Cells that do not equal a specific amount
​
Say we wanted to find the number of Projects within the United States. We do not have a Column that lists the Country the Project will be completed in but we can quickly see that the only city outside of the United States is London. We can now use the formula to find the total number of Projects that are NOT in London by entering 

=COUNTIF(A2:A11,"<>"&A3)

The end result is 7 Projects within the United States.
Building off the original worksheet from here on let’s continue with the following data

Using wild cards

Notice in the worksheet that Project Type is usually written out as Renovation, however, in Row 9 it is written as “Reno”. Inconsistent data is all too common in large spreadsheets making it impossible to count the number of Renovation Projects by using either the exact wording match or the match cells conditions used earlier. If the data is inconsistent Wild Cards may be utilised.
The first wild card is the asterisk (*) The asterisk is used to find cells that have the same beginning or ending characters. To find the number of Renovation Projects enter:

=COUNTIF(B2:B11,"Reno*")

this formula is telling Excel to search Column B for any Projects that begin with “Reno” and under the Counts total in the above screenshot the number is 4.

The second wild card character is the question mark (?). The question mark is used if you need to find the count of cells that you know either begin with or end with specific letters or numbers and have an exact same amount of characters. To see the total Projects in London you could enter the exact wording match, match cells or you could enter:

=COUNTIF(A2:A11,”????ON”)

With this Excel will search for any 6 letter words ending in ON within Column A. Should your data have an asterisk or question mark within the cells the COUNTIF Formula can still be used by inserting a tilde (~) in front of the question mark or asterisk in the formula. Excel will then treat the question mark or asterisk as an actual character versus a wild card.

Multiple Conditions

Using the addition sign (+) tells Excel to search for more than one condition.
To determine the number of permits that must be completed during the fourth quarter, enter:

=COUNTIF(D2:D11,D2) + COUNTIF(D2:D11,D10) + COUNTIF(D2:D11,D3)

Telling Excel you want the total count for all Permits with a due date of October, November and December. Out of the 6 permits due, 4 are due within the 4th Quarter.

Working with dates using the COUNTIF Function

The formulas that have already been mentioned will work the same using dates. Plus you can combine the COUNTIF Function with Excel Date and Time functions to find the number of projects that have already started as of today, the number of projects that have not started as of today or the number of projects that started in the first quarter. Using the Start Date data we enter those formulas using the TODAY() Formula as such:

Number of Projects that have not started as of today =COUNTIF(C2:C11, ">"&TODAY()) Showing 3 Future Projects.

Number of Projects whose Start Date has passed =COUNTIF(C2:C11,"<"&TODAY()) Showing 7 Past or Present Projects.

To find dates within a specific range utilise the Counting Cells in between two values formula as previously mentioned and enter the dates as the values. For all Projects with a Start Date during the first quarter:

=COUNTIF(C2:C11,">=1/1/17") - COUNTIF(C2:C11,"<3/31/17")

Showing 7 total Projects started during the first quarter.

Using COUNTIF Function to help clean up data

To find the number of mistakes within a worksheet without having to scroll through all the data you could utilise COUNTIF Functions to check for mistakes.

Finding missing data:

=COUNTIF(A2:E11,"<>"&"*")

The formula above lists all blank cells within the entire worksheet, however, by searching each column one at a time you can skip over the columns without blank cells. Go through the columns that yield a count of blank cells and enter in the appropriate data. Likewise, removing the asterisk from the equation will provide the total number of cells that are not blank.

Finding duplicate data:

COUNTIF can find duplicates within one or multiple columns and rows. For example, let’s assume the company never has a project start on the same day. Search the worksheet for repeat dates. The simplest way is to add a column to the worksheet titled duplicates. The formula to test for duplicates is:

=COUNTIF(C2:C12,C2)>1

Excel is Searching all of Column C for any date that matches the date in C2. Repeat this process down the Duplicate column and any TRUE values can easily be located as duplicates. The result shows a duplication of C5 and C12. To quickly search for the TRUE values, Sort the data in Column E from Z to A and all duplicates will be at the top of the worksheet. If necessary, all duplicates can be deleted.

To get a total of all duplicates enter:

=COUNTIF(E2:E12,”TRUE”).

Now, i'm about to get a lot more complicated but just trust the formula! Let's Count duplicates in multiple columns, we enter the following:

=SUMPRODUCT((COUNTIF(A2:A12,B2:B12)>0)*(B2:B12<>""))

This results in a 1 showing one single duplicate between Column A and B. That duplicate is Tampa accidentally being entered as the Project Type and can easily be remedied. This same duplicate could have been found by searching for duplicates within a row by entering:

=SUMPRODUCT((COUNTIF(A2:E2,A2:E2)>1)*(A2:E2<>""))

Repeat for each row to find any duplicates within the rows.

Hopefully, these examples on how to utilise the COUNTIF Function have shown the wide array of uses. If you run into any problems with the formulas be sure to check the following common mistakes.

• Make sure there are no spaces between any characters in the formula
• Double check that the parentheses have been closed
• Ensure the operators are enclosed in quotations

I hope you found the article useful, let me know if you have any questions and before you head, sign up below for my free book 'The only 27 Formulas a professional needs to know'. 

Sohail

The post Using the COUNTIF function in Excel: Beginner to Advanced appeared first on Earn and Excel.

How to Make a Pie Chart in Excel 2010

I’m a big fan of Pie Charts because they are a great visual representation of ‘spread’ or ‘distribution’ of data. By this I mean you can understand how different parts contribute to the whole. Rather than throw more fancy words at you, here’s a few visuals to explain further:

You want to depict the Mobile Phone market share, taking some numbers from 2016 we get:

The option to include or exclude numbers from Pie Charts is useful depending on whether you are aiming to show deep analysis in your reports or spark conversation.

Another example would be a Pie Chart that belongs on a Project Management dashboard to show the distribution of Red, Amber and Green rated projects. Now we don’t have to have numbers, but the visual can set the tone for a meeting that will be discussing the progress of the projects.

Enough banter on Pie Charts, how do we actually make them?!

To generate a pie chart in MS Excel 2010, execute the following 3 steps.

I'm going to randomly pick the topic of Nepalese ethnic groups to build our Pie Chart. Enjoy!

1. Start by listing the data in a worksheet as in the picture below. This is pretty easy, one order can be your forms of the ethnic groups, and one other order is their corresponding percentage.

For example, to create a spreadsheet featuring the percentage of ethnic groups and the percentage you are able to write “Newar” in cell C7 and "30" in cell D7 and so on. Please note: All data values are more than zero and you will find no clear lines or columns.

Click to enlarge

2. Now pick the data you want to be represented in the pie chart: To select the data, click the top left of the effective data and then pull the cursor to the bottom right. In this case, I have selected the data range C7: D12

Click to enlarge

3. And once everything is selected, Click the Insert ribbon and then Click Pie, then pick the basic 2-D option (3-D versions have the potential to cause confusion in audiences so stay away from them) and voila! A pie chart will be automatically placed into your worksheet.

Click to enlarge

As ever with Excel if you happen to change any of the data, that change will ripple through automatically in to the Pie Chart.

Let's look at some of the customisation options for the Pie Chart

Customise the Pie chart from the Design tab

Whenever you click the pie chart you will be shown the 'Chart Tools' menu on the ribbon. The three tabs being: Design, Layout and Format.

Click to enlarge

Next Click on the "Design" tab under Chart Tools to see the Chart Styles cell and Chart Layout panel. Several style types can be seen in a drop down box when clicked. You can search up and down through the alternatives and find something suitable for your report/dashboard. In the case, I've picked the Layout 6. 

Click to enlarge

Similarly, you are able to press the "Format" tab in the Chart Tools element of the ribbon. Select "Shape Fill," "Shape Outline" or "Shape Effects" to decide on the visuals of the pie chart. In the figure, I’ve selected the second shape styles..

Click to enlarge

Adding Labels to your Pie Chart

If you need more customisation for the pie chart, Right-click the chart and select "Format Data Labels." A pop-up dialogue box starts featuring the label options. Select "percentage" to see the values indicated as a percentage of the entire pie chart, or select "value" to alter the phase labels to their numeric values as shown in the spreadsheet.

You can select or deselect some other labelling possibilities to suit your preferences. As an example, checking "Series Name" or "Category Name" includes these names to the phase labels. Choosing one of the Label Position possibilities changes where the phase labels appear. In the example, I've selected the Category name in the label options.

Click to enlarge

Thanks for reading!

Before you go, get my report '10 things I did with Excel that increased my salary from £27K to £100K'

​

The post How to Make a Pie Chart in Excel 2010 appeared first on Earn and Excel.

Combine Cells and Columns in Excel with Merging

The Excel INDIRECT function

Why Online Excel Courses Often Fail For Professionals

There are countless resources online for improving your Excel skills: Excel Courses, E-Books, Google, YouTube  and a billion Blogs, it’s overwhelming to say the least. So how do you wade through it all and make some progress? If you are thinking of learning online or perhaps you are not getting very far with books and googling then this article will make you aware of the pitfalls before you invest time and money in improving your Excel skills.

First, ask yourself why you want to get better at Excel

Fundamentally, there needs to be a reason to get better at Excel, you need to think Strategically“Strategy without tactics is the slowest route to victory. Tactics without strategy is the noise before defeat.”― Sun Tzu

Almost every Excel course or Book wants you to get better at Excel (duh, of course).But, almost all of them fail at helping YOU answer the question ‘Why do you want to get better at Excel?’Most people’s response to that question is along the lines of…

• I want to get better at Pivot Tables
• I want to do V-Lookups
• I need VBA skills

Frankly, those are poor reasons and most Excel Courses, trainers or Excel book authors will say…’Sure, I can help you with that’. Now, I help professionals get better at Excel and frankly I find that kind of advice and support to be poor.Since, I have been using Excel for over 10 years in the biggest and best organisations like Investment Banks, I know a thing or two about how Excel is used. So, whenever someone comes to me in the hopes of improving their Excel skills, I challenge their reasons and ask ‘Why?’Then I’ll get a slightly better response like:

• I’m a financial controller so I need to do Pivot Tables
• I’m a PMO Analyst so I need to do V-Lookups
• I’m a Reporting Manager in Banking and I need good VBA skills

Slightly better, but the reasons are still a bit random. When you are just learning random skills, that is very tactical thinking and like Sun Tzu said above, tactics without strategy is the noise before defeat.Excel skills, or should I say, the RIGHT Excel skills learned for the RIGHT reasons did wonders for my career, because they fit in to a wider strategy of career progression. I’ve had plenty of people approach me who I’ve told to not focus on learning Excel because it wasn’t the right move. 

Not all Excel Teachers are created equally

For example, I had a senior Financial Controller with ambitions of becoming a Finance Director in a Bank ask me what Excel skills she should improve. I asked some questions and discovered she had a small team including someone who was good at Excel but she wanted to carry out some of the more sensitive data analysis herself. I suggested that her problem wasn’t a lack of Excel skills but poor delegation skills and that improving her delegation and building trust with her team was going to get her promoted much quicker than devoting time to learning Pivot Tables and Macros!This isn’t advice the average Excel course provider or Excel book author talks about. Why?Because they are Excel specialists.If you are a professional like an Analyst, Controller, Accountant, PMO/Project Manager or Management Consultant then you need solid Excel but do not put your skills, your career in the hands of an Excel specialist who doesn’t get the bigger picture of how Excel fits in with your career.They will have you learning things you do not need and send you down time-wasting rabbit holes. Worse still they won’t tell you what to focus on or how to market your skills.

Pros and cons of learning Excel via Google and YouTube

The biggest issue faced by students is that the internet has given them access to cheap or free information.Let me tell you NOTHING is more costly than cheap or free, especially if you are learning things from the wrong people. But more than that, while there are some good free resources on the internet, I have found that free is highly unstructured, random and often causes confusion and overwhelm. And that’s when it’s good! When it’s not good then it’s a waste of time, it doesn’t get implemented.The single most important factor when learning a subject like Excel is structure and curriculum. The wrong structure and curriculum won’t leave you any better off especially if you are a professional who needs to understand how to actually apply Excel to real scenarios, not just “Kendrick has 10 apples and Drake has 13 oranges” type of problems.If you are very young, don’t have funds but have the time, then use Google to slowly get there. If you are a working professional, take the shortcut, invest in a good course with someone you have access to. Ideally someone who can see where Excel fits in the wider context of your career and learn from them.

The best way to use Google

I’m not anti-Googling and I’m not saying it will prevent you from greatness but I use Excel with Google in a very specific way. For example, if I have to do a complex formula or complex bit of VBA/Macros code it would have taken me 30-40 minutes to work it out. It takes me 5 minutes to Google it and 2 minutes to tweak it so I save time. As you systematically raise your Excel game, your Googling will be more and more effective and you will save more and more time. Here’s a graph to explain this:My philosophy with Excel and work is all about saving time so yes, I use Google but to speed things up and I can do that because I built a foundation of solid Excel skills.       Advanced Excel VBA 

Advanced Excel VBA can be a massive undertaking and most of the people who teach it do a terrible job. I’ve seen Excel VBA courses start teaching you the jargon first, they get you used to the environment. That really is ridiculous, it shows a complete lack of empathy from the trainers. VBA is a life altering skill. I don’t say it lightly that it helped me go from a £27K a year salary to a £100K a year salary. It’s done similar for many my students and colleagues and when it’s mis-taught then that’s a missed opportunity.The other thing that is mis-taught is teaching ‘perfect code’ and solutions to professionals.That’s just plain bad.I’ve had people numerous ‘experts’ over the years tell me that my code was messy and clunky. I completely agreed with them BUT I got the problem solved rapidly, sure I can take my time and build an elegant solution but 9 times out of 10 when that isn’t the focus of your job as an Accountant, Project Manager etc then it’s a poor use of your limited time.

My recommendation for Professionals needing to learn Excel

Having worked in big organisations for over a decade and solving real world problems with Excel, I can tell you that there are three kinds of problems that require Excel to solve and four kinds of Excel solutions:

The three types of Problems are:

1. Tracking or organisation of data.

2. Analysing data whether that’s regular analysis or ad-hoc analysis. Forecast, variance analysis, or for example, you’re working on a project and need to know what milestones are going to deliver in several months’ time.

3. Communication of data, i.e. reports and MI.

And those three problems only need four kinds of solutions to tackle them:

Four kinds of Solutions

1. You are going to find the information. That means you are going to look it up or find it.

2. You are going to treat that information with rules i.e. you find some information, do some analysis saying, ‘I want this data to do these calculations or I am just looking for this, if it looks like this then I want that.’ Those are very important things the if and the then.

3. Deal with the non-numerical information that most of us analysts, accountants, controllers, PMO’s, and management consultants are often dealing with i.e. names or product codes.

4. Summarising your data after your analysis i.e. a table, chart or graph. i.e. the end game

This framework is a more strategic view of how to use Excel, rather than just jumping to ‘should I use VLOOKUPS, Pivot Tables? etc’. This approach allows to approach Excel in a wholesale manner than piecemeal (here and there, where you learn-forget-learn-forget).

 Conclusion: Excel should be thought of as stepping stones to greater things…

What I benefited from most and consequently teach my own students is that Excel presents a very unique opportunity to:

SAVE TIME

This should be the goal for a professional who works with Excel on a daily basis. So, you want to learn Excel, then learn at least some VBA which will help you save time, then repurpose that time to adding value in your organisation, impress the right Execs and open doors up.If you would like to sign up for one of my Excel courses then you can learn more here:

The post Why Online Excel Courses Often Fail For Professionals appeared first on Earn and Excel.

Dollar $ in Excel – Absolute and Relative References

Excel’s default is to use relative reference when using formulas. That means that when a formula is copied and pasted into another cell the formula will change its reference points by the exact number of columns and rows that you moved the formula.

We achieve this by including or omitting the dollar sign.

Demonstrated below in the left worksheet we entered the formula B1*A2 and in the right worksheet we copied that formula down into C3 but Excel changed the formula to B2*A3 (One row down the column, just like we moved the formula)

How do we stop this change from occurring? Using absolute reference and learning where to insert a dollar sign ($) we can tell Excel exactly what elements of the formula we want to stay the same as we move from cell to cell. Cell referencing is about linking one cell based on the current cell. It is mostly known for altering formulas. For example, the below worksheet shows =B$1*A2 entered into C2, thus, linking B1 to C2. If B1 is altered in any way C2 will also be altered.

By adding the dollar sign into the formula you can tell Excel to keep the row the same, keep the column the same or keep the row and the column the same.

Keeping the Row the same:

Enter the dollar sign before the Row Number in the equation to keep the row the same. In this instance, we want to keep B1 as the cell that is multiplied by all other cells. We enter =B$1 to show this as we move down column D. If you were to move this formula to Column E the reference will stay in Row 1 but it will also move to Column C because we did not make Column B an absolute reference. The same is true for the second half of the equation instead of Column A being the reference it would now be Column B.

Keeping the Column the same:

Enter the dollar sign before the Column Heading in the equation to keep the column the same. Here we added a few more numbers to row B and now want to multiply down the column. We enter $B to tell Excel this.

Keeping both the Row and the Column the same:

Combine the above two placements of the dollar sign to keep both the column and the row the same in the formula.

Enter $B$1 to tell Excel to use the exact Column and Row as in the original formula. No matter where in the worksheet you copy the formula the B1 will be used as the multiplier, however, the second value (A2) will change because it is a relative reference.

Tip: When typing your formula, you can hold F4 and toggle from relative reference to any of the three styles of absolute reference listed above. For Example, enter =B1 and hit F4 it changes to =$B$1, hit F4 again and it changes to =B$1, hit it again to get =$B1, and one last time to return to =B1

Using Absolute Reference

In the example worksheet below, the company bases its future advertising costs on last year’s sales. Twenty-percent of the previous year’s sales will be allocated to the new year’s advertising costs. We are going to sum each district’s sales for the year by making the row range an absolute reference. Then sum each district’s sales for the month making the column range an absolute reference.

Finally, setting both the column and row as an absolute range we will the total sales per month by the 20% Advertising costs.

Total District Sales for the Year:

1. In Row 14, Column B enter =SUM(B$2:B$13) to add all values in Column B.
2. Copy across Row 14 and Excel will keep the Row Range (2 -13) the same but will change the columns.

Total Sales Per Month

1. In Cell E2 enter =SUM($B2:$D2) to add the values across the Row 2.
2. Copy down Column E and Excel will keep the Column Range the same (B-D) but will move down each row.

Calculate Advertising Costs per Month

1. Enter the 20% into any cell (this example it’s entered into B15)
2. We will use the data from Column E since it is a sum of the three columns before it.
3. In F2 Enter =E2*$B$15 telling Excel that as me move down Colum F we want to multiply each row by the data in B15
4. Double-clicked the bottom right of the cell and Excel will auto-fill the remaining pertinent cells within Column F.

Now we have a total for each month’s projected Advertising Costs. Copy the formula from District Sales for the Year over to E14 and we now have the Total Projected Advertising Costs for the next Year

Using Absolute Reference with Multiple Worksheets

In the above worksheet (Sheet 9), we have the 2018 Projected Costs for each month. We want to use our original worksheet (Sheet 8) where we projected the advertising costs to fill in Column F for Advertising.

1. In Cell F2 of Sheet 9 above enter the equal sign (=)
2. Either Click in Sheet 8’s Tab and select January’s Advertising Costs (F2) or type Sheet8! Followed by $F2 or =Sheet8!$F2
3. Either double-click the bottom right corner of Cell F2, click and drag down the column to auto-fill the Projected Advertising costs per month or copy the formula and paste into each cell.

Changing the referencing of a formula to absolute is quite simple as you have seen. The key is to know exactly how you want your formulas to work. If you know that you want a range of data to be multiplied by a single cell then you know to use absolute referencing, which, is a lot less time consuming than creating a column next to your range and adding the multiplier down that column and working across the rows to get your answer. Absolute referencing will make your worksheets look much cleaner and well-organized.

Before you go, if you want to learn more about how Excel References work then feel free to check out our Formulas Book 'The only 27 Formulas a Professional Needs to Succeed'

The post Dollar $ in Excel – Absolute and Relative References appeared first on Earn and Excel.

How to Alphabetize in Excel 2010

How to Use Conditional Formatting in Microsoft Excel 2016