Using SUMPRODUCT Function to Count Records using Multiple Conditions

There are many ways to count records using multiple conditions in Excel. These can range from using the COUNTIFS function, PivotTables or by simply filtering the data. However neither of them are as flexible as the SUMPRODUCT function.

The SUMPRODUCT function is used to multiply the values of multiple arrays and then sum the totals. However it is capable of so much more amazing feats. It truly is one of those hidden gems waiting to be discovered.

Continue reading

Learn How to Use Excel – 3 Special Excel Functions that will Amaze You


Excel contains 400+ functions and this list is constantly growing. There are functions to perform almost any task from financial, date and time, statistical etc.

This learn how to use Excel post looks at 3 little known special Excel functions that will take your skills to another level and make you the envy of your work colleagues.

Continue reading

Excel Training – Lookup your Data with the DGET Function

A key skill in Excel training is to be able to lookup and retrieve data from a range of records. The most popular way of achieving this is to use the Vlookup function. The Vlookup function on Excel is awesome and easy to use, but it has its drawbacks.

Cue the DGET function. A very powerful Excel function that will retrieve data from a record without the limitations of Vlookup. Advantages of using the DGET function include:

  • It can retrieve data from a column to the left of the column you searched within.
  • It can lookup data based on multiple conditions.
  • It can handle both AND and OR logic.

Continue reading

Count the Number of Words in a Cell

Working recently at a large Internet company they needed to find how many words were in a cell. This was because they had imported hundreds of thousands of keywords that customers had used to find their site through search engines.

To analyse this data they wanted to count how many words were in each cell containing keyword searches. provides many text functions for managing and manipulating the text in the cells of your spreadsheet. The following formula did the job.

Continue reading

Basic Excel Skills – Insert a Happy Face using the IF Function

I was asked today if it was possible to write an IF function that could insert a happy face if certain criteria was met. This would be used to show good performance. An unhappy face would be inserted for poor performance.

To insert a happy face in Excel we need to enter a capital J and format the cell in a Wingdings font. For an unhappy face, it’s a capital L formatted in the Wingdings font. Continue reading

Unleash the Power of Excel’s CELL Function

A very powerful function in Excel that is unknown by many is the Cell function. Use the Cell function to find information about a cell. This can include the cell’s number format, row number or whether it is locked or protected.

There are times when this kind of functionality becomes extremely useful. Especially whilst Excel does not provide many other means of finding the number format of a cell, or whether it has been locked. Continue reading

Perform Quick Calculations in Excel

It is possible to perform quick calculations in Excel without having to write any formulas. This technique is brilliant and provides the formula result you need quickly and easily.

You can count, sum, average and find the maximum or minimum number in a range simply by selecting the numbers you want to calculate. Continue reading

How to use the Trunc Function in Excel

The Trunc function in Excel is used to reduce the number of decimal places on a number without rounding.

Excel provides a few different rounding functions such as Round, RoundDown and Ceiling to cater for all your rounding needs. However the beauty of the Trunc function is that you can set the number of decimal points for a number without rounding. Continue reading