You won’t always want to apply a text string as the lookup value. If column B contains the value “Merchant 1”, return a count of the number of times it appears. If column B contains the value “Merchant 1”, return an average of column D (commission) for that merchant.

If column B contains the value “Merchant 1”, return a sum of column D (commission) for that merchant.

With these particular formulas you first supply the range that you wish to lookup, the criteria that you wish to match in this range, and then finally supply the associated result. To demonstrate these I’ve put together a simple affiliate commission report below: Some of the most common Excel IF formulas allow you to sum, count and average data based on set criteria. So now the formula is laid down, let’s get into some of these great uses…! 1. Equal to, Greater than, less than, greater than or equal to etc. Different numerical operators can also be used well in these types of statement. Of course you can return more sophisticated results than a simple number or text string if the criteria is met, but we’ll get into those later. If the number in cell A1 is greater than £10,000, return “high spend”, and if it isn’t then return “low spend” If the value in cell A1 is equal to “March”, then return a 1, and if it isn’t then return a 0. – Lookup an associated cost IF a cell contains the word “forecast”Įxcel will tell you that the basic IF statement follows this format:īelow I’ve applied this to some very simple statements: – Return the phrase “Out of Stock” IF the stock level field = 0 – Return the sum of all clicks IF the month is March The main essence of the statement is to return a result when meeting certain criteria. It can be used to quickly fetch results based around given criteria, to sort and catalogue data, or be used in conjunction with other formulas for some very inventive and powerful operations. Getting acquainted with the many IF statements will start to open up a whole range of new opportunities for you when you’re looking to analyse and manipulate data. They will be especially useful if you work in web marketing and regularly handle keyword and URL data, but there’s plenty there for other fields too. In this post I look to tackle the most common uses of the IF statement.īy the way, if you’re a more advanced Excel user and want to explore beyond the IF formula, you may enjoy Found’s latest list of advanced Excel formulas and macros. In my previous post I looked at some top tips to save you time in Excel, ranging from data manipulation, to cell formatting and general navigation. I’ve also attached a worksheet to this post for you to see and work with the examples themselves. You may be well versed in Excel and know much of the below already, but have a look through as I’ll be touching on a number of additional hints and tips along the way as well. Hello, and welcome to my second post in this series, where I dare to stand up against some of the most common Excel challenges we face within this industry, and defeat them head-on with cold, hard, uncompromising logic.

If then formula in excel with text