There are many ways to count with multiple conditions in Excel. These can range from using the COUNTIFS function, PivotTables, or by simply filtering the data. However, none 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 in Excel waiting to be discovered.
Excel contains 500+ functions and this list is constantly growing. There are functions to perform almost any task including financial functions, date and time functions, and statistical calculations.
This 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.
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 in Excel is awesome and easy to use, but it has its drawbacks. Cue the Excel 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:
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.
The following basic Excel skills demonstrates using this technique to display a happy or unhappy face dependent upon someone’s exam grade. The exam grade is in cell B3 and the smiley face will be inserted into cell C3.
[Read more…] about Insert a Happy Face using the IF FunctionIf you wish to protect the cells on a worksheet that contain formulas you will need to lock the cells first, whilst also ensuring that the cells users should be able to change are unlocked.
It may have been a while since you worked on this worksheet and you are not sure which cells are locked and which are not. You need a fast method of locating the locked cells. Let’s use Conditional Formatting to highlight the cells that are already locked so they are easy to identify.
[Read more…] about Locate the Locked Cells on a Worksheet
