The COUNTIF function is one of the most useful functions in Excel. Its job is to provide conditional counting. This is primarily used for analysing data and producing reports and dashboards.
In this blog post will look into 5 more unorthodox but useful scenarios for the COUNTIF function to be used. The 5 COUNTIF examples we look at are;
Prevent duplicates in a range. Uniquely rank items in a list. Count the unique entries in a list. Compare two lists. Identify names that occur 3 times or more.
You may have scenario on a spreadsheet where you only want to sum the negative values in a range.
Take the example below for instance, it contains a list of incoming and outgoing payments. If we needed the total for outgoings, we would need to sum the negative values only.
This can be done using the SUMIF function. This function allows us to sum only the values that meet a specific condition (
new to SUMIF? find out more).
The formula below will total the outgoings in the list by summing only the values less than 0.
The criteria has been entered as text in double quotes. When entering criteria like this into the formula it needs to be entered as text.
The criteria specifies that only values less than 0 should be summed.
Watch the video VIDEO
PivotTables are one of the most useful tools in Excel. They allow you to easily summarise, examine and present a complex list of data.
This blog post explores 5 advanced PivotTable techniques.
Grouping fields by month and year Calculating data as a percentage of the total Using Slicers Applying Conditional Formatting to PivotTable data Creating calculated fields
Add drop lines to a line graph in Excel to connect the data point to its label on the axis. This extra visual chart element can make it easier to view the data on busy charts.
Take the line graph below for instance that shows the call volume for 14 hours of a day. It can be awkward on this chart to quickly view the time of day for a specific data point.
This tutorial looks at how you can create a macro in Excel to export all sheets to PDF. Each sheet of a workbook will be saved into the same folder. The name of the worksheet will be used as the filename of the PDF.
By creating a macro this seemingly repetitive task can be performed at the click of a button.
If you have ever tried to
add a hyperlink to a userform, you would have noticed that there is no hyperlink button on the toolbox.
You may not be able to insert a hyperlink like you would on an Excel spreadsheet, but you can create a link by inserting a label and applying the OnClick event.
Adding a Hyperlink to a Userform
In this example we will add a hyperlink so users may email an enquiries department from the userform.
When using a PivotTable to summarise a large list of data, you may need to count unique occurrences in a particular field.
For example, you may have a list of data like the one below and want to count the number of different customers in each country, or the number of different products that were sold in each country.
If you added the
Customer Name field to the Values area of a PivotTable and summarised it with a Count you would get the result below.
However this is counting all of the transactions in each country. We need to
count the unique customers making those transactions in each country.
In this blog post we look at how to create a macro that will look in a list and
format all the instances of specific words that appear in a cell.
This macro is case sensitive and will also only apply the formatting if it is the whole word. For example, if you are formatting all instances of the word ‘red’, this macro will not format it if used in words like ‘reddish’ and ‘sacred’.
Using the SUM function to total a column of values is an essential skill in Excel. However, it is not always that straightforward. The column of values may contain errors, or the values you want to sum may be on many different sheets.
This article looks at 4 advanced techniques for summing values including;
Summing a range that contains errors Creating a cumulative total A consolidated sum from multiple sheets Summing Every Nth Value in a Range
Create a battery chart in Excel just like you see on your smartphone, tablet device or even PC. It is a great visual way of viewing the percentage remaining of a value.
In Excel a battery chart could be used to view the amount remaining of a budget, the work remaining on a task, or number of people still to attend training.
There are many reasons why you may want to visualise an amount dropping until it is empty, or complete. A battery chart provides a method that people can relate to easily.
The image below shows a battery chart being used to show the amount outstanding on a training budget.