EXCEL IF THEN FORMULA EXAMPLE HOW TO

vlookup('smith3', lookup range, 2, false) would tell us the 3rd phone number of smith. Next, when looking up smith instead of using the initial column of customer data we will use our modified customer data to fire the lookup, for eg. įirst in our data list we will insert another column and place the formula =current-item&countif(range till that point, item), this will append the number of smiths till that point to the end of smith, thus first smith would become smith1, second smith would become smith2, so on… Getting second, third, fourth or nth occurrence from the list can be tricky, by using a combination of countif and vlookup we can lookup nth occurrence from a list. customer phone number data that has grown over a period of time with new numbers added at the bottom of the list. Often we work with data that has lot of duplicates, for eg. Lookup second, third … nth occurrence of an item in a list in excel: This can be a quick way to do pivot analysis of data without actually using excel pivot tables (very useful if you are allergic to excel pivot tables or not very happy using and constantly updating them)Ĥ.

EXCEL IF THEN FORMULA EXAMPLE HOW TO

Now that we have figured out how to use sumif and countif, you can use these two functions to create quick summary of your data.įor example, we can findout, average sales per customer per city dividing total sales data per each city (obtained using a sumif) with total customers in that city (obtained using a countif) as shown below. Quickly Summarize Data with countif / sumif: countif($g$32:$g$47,'chicago') would tell us how many customers are in Chicago. The formula looks like =countif(condition rage, condition), eg. In the above example, I have used countif function to findout how many customers are there in each city (where customer data is in List B and city data is in List A). Excel countif formula counts data that meets a specific criteria. This can be done easily using a good old countif function. Often when you are working on data spread across multiple sheets, it helps to know how many times each item on one list is repeated in another list(s). Count how many times each item on list A is in List B vice versa Now we can use this column to test our condition by writing the sumif function as =sumif(condition range, 1, sum range) 2. (just enter 1 and 0 in 2 rows, select both of them and drag till the end of the table).

In our case the criteria will be, “if the data element is in odd number rows”.Īll you need to do is add an additional column at the end of the table and fill it with 1s and 0s. Sumif function sums a range of data that meets a specific criteria. Thats why it helps to know that you can sum alternative rows / columns of data using sumif() formula.

when you copy paste your credit card statement in excel), Ofcourse, we can always type the sum function with all those arguments, but we would rather chomp on that donut while excel does the dirty work for us. There comes a time when you are slapped with a sheet of data and need to sum every other row in it (dont ask me why, it happens, for eg. And IF formulas are a majority of these, so it doesn’t harm to learn a few interesting things you can do with just the excel IF functions. But, most of us(well, just me then.) use just about 5-6 formulas to do our jobs. Excel has various functions, including functions to calculate inverse cosine of a given value, to multiply 2 matrices, to estimate the internal rate of return.

Excel if then formula example