Learn Excel IF Formula – 5 Tricks you Didnt know

Posted on June 9th, 2008 in Featured , Learn Excel - 41 comments

using excel if formulaExcel has various functions, including functions to calculate inverse cosine of a given value, to multiply 2 matrices, to estimate the internal rate of return. But, most of us(well, just me then..) use just about 5-6 formulas to do our jobs. 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.

1. Sum alternative rows / columns:

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. 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. Thats why it helps to know that you can sum alternative rows / columns of data using sumif() formula [syntax and examples]. Sumif function sums a range of data that meets a specific criteria. In our case the criteria will be, “if the data element is in odd number rows”.

howto-sum-alternative-rows-columns-excel-using-sumif

All you need to do is add an additional column at the end of the table and fill it with 1s and 0s. (just enter 1 and 0 in 2 rows, select both of them and drag till the end of the table). Now we can use this column to test our condition by writing the sumif function as =sumif(condition range, 1, sum range) [learn how you can highlight alternative rows / columns in an excel table]

2. Count how many times each item on list A is in List B vice versa

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). This can be done easily using a good old countif function. Excel countif formula counts data that meets a specific criteria. [syntax and examples]

learn how to using-countif-ms-excel-function

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). The formula looks like =countif(condition rage, condition), eg. countif($g$32:$g$47,"chicago") would tell us how many customers are in Chicago.

3. Quickly Summarize Data with countif / sumif:

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.

For 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. 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)

data analysis with countif sumif spreadsheet functions

4. Lookup second, third … nth occurrence of an item in a list in excel:

Often we work with data that has lot of duplicates, for eg. customer phone number data that has grown over a period of time with new numbers added at the bottom of the list. 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. [VLOOKUP tutorial and examples]

First 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…

4-find-second-third-nth-occurrence-using-vlookup

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. vlookup("smith3", lookup range, 2, false) would tell us the 3rd phone number of smith. Note the last argument to the vlookup as “false”, since our list may not be alphabetically sorted, we have to use “false” to force excel to keep looking till it finds the 3rd smith row.

5. Reduce your nested if()s to one function

Do you know that you cannot next excel if functions beyond 7 levels? Thankfully, most of us never go beyond 3 or 4 levels. But why write even that many levels when you can use choose() function, which is like a switch structure in programming. A typical switch function would look like, =switch(condition, outcome1, outcome2, outcome3...), for eg, =switch(3,"good","average","poor") would return “poor” when used. The only limitation to the switch() excel function is that it accepts numbers for testing the condition. But you can overcome this with some creativity, like I have shown in the below student letter grading example:

5-using-excel-choose-function

How did I convert the letter grade to a numeric in the choose(), well thats for you to figure out :)

What is your favorite IF formula trick?

Well, that is for you to tell me. What is your favorite Excel IF function tip / usage? Share it in the comments, let everyone know

Download IF Formula Examples and Play with them

I have prepared an excel sheet with all these if function examples, Click here to download it and experiment.

Learn More:

| More
Excel School - Online Excel Training Program


Trackbacks & Pingbacks

Comments
John Satta June 9, 2008

Just wanted to say I love your excel tips – always thought I was an excel guru, but my hat is off to you!

Wish I could think of a good IF tip so I could contribute :-(

Great stuff – keep it up!
-John

Dissappointed M&A part III June 10, 2008

Buddy,

- Thought this would be the best place to catch you as this is the only place i can find that u still come. Did you get a chance to go through the “viral note” i had send you. Well the note was prepared when i was down with viral fever and had nothing else to do.

But my excel demi god i didn’t realise you would eat it up with your ever increasing hunger for useless stuff and discharge it the next day. No response to my sms, or any of the messenger msgs.

Waiting for your blessings like a melancholic lover for his maiden to return

Keith Dsouza June 10, 2008

Nice tips, most of them will be quite useful to me :)

Chandoo June 11, 2008

@John Satta … that is so kind of you, thanks for your comments.. :)

@Keith … Welcome to PHD, thanks :)

@M&A … hehe, responded.. stop being cryptic and come out in public :D

Anelisa August 11, 2008

I would like to determine a catergory by selecting the first two digits from a figure e.g. from 011000, 020000 – if it’s 01 then I’ll write Educator in another collumn next to the figure, 02 = Practitioner.. etc

Chandoo August 11, 2008

@Anelisa: Hope you have already figured this out, otherwise here is how you can do this:

=if(left(figure,2)=”01″,”Educator”,if(left(figure,2)=”10″,”Practitioner”…

let me know if this doesnt help :)

guest November 19, 2008

Nested if’s:
Nice, but still suffering from the limitations of choose (29 param’s max)!
Why not using index:
=INDEX(grades;CODE(D99)-64;)
where grades is the matrix with the grades?,
drefsa

Ketan November 19, 2008

@ guest :
You may use the offset command too
=offset(grades;CODE(D99)-64;)
Where grades is name of cell below which all the required grades written for A,B,C,D,F….

guest November 19, 2008

@ ketan,
sure, works as well.
My inital comments was meant to be as close to the logic of the examples given as possible.
generally I cannot befriend myself with more than 3 nested ifs. better to switch to index, offset, or vlookup solutions for numerous reasons.
But I have question though:
the code() function uses the ANSI – set on windows. What about other systems like MAC? Is it returning the same results? Sorry-but I am lacking a MAC…
drefsa

Chandoo November 19, 2008

@Guest: Welcome to PHD and thanks for sharing your ideas. I like the index, lookup based solutions compared to normals ones. But as with any solution these are also good for a particular context – in this case, large enough data.

Btw, CODE() returns ASCII code of the character in the input and it has got nothing to do with the computer you are using. ASCII is a standard code across all computers and used to represent various characters with numeric (actually hex) values for internal representation.

guest November 19, 2008

Chandoo,
you are right, a solution always depend on the given problem.
One thing puzzles me:
in the German XL-help it says for Code(): WIndows Ansi, MAC Mac charset.
If the numbering is of the characters is the same all will work fine of course. Just wondering.
drefsa

Chandoo November 19, 2008

@Guest: I am sorry for misleading you. You are right. CODE() returns ANSI or Mac Character codes based on the system you use.

But thankfully, they return value would be same for most of the day to day characters and is equal to ASCII code.

There are quite a few standards when it comes to internally representing characters in 8 bits – like ANSI, ASCII, MacRoman Set, ISO-8859-1 etc.

See this for more: http://www.alanwood.net/demos/charsetdiffs.html#f

but rest assured as for most values code() would return same output no matter what OS you are using.

guest November 20, 2008

chandoo,
thx! that’s a useful link.
I’ll check out the “non-conformities” eventually. In my opinion it is just iseful to be really clear on the potential pitfalls of a method. On the hand: XL sometimes reminds me of a inexhaustible bag of surprises ;-) )
btw: like ur blog
drefsa

CraigM December 5, 2008

Chandoo thanks for this.

I have a fairly large spreadsheet that this has helped greatly speed my work up.

There are a couple of things that would make it complete. I have about 7 different items that I would like the high, low and median prices for. Is their an easy way, not unlike this, that will extract them from an unsorted list?

Thanks

Craig

Satish December 23, 2008

Dear Chandu,

I like you simple and direct examples, I am still mid-way, I liked you example No 3 in this Page( 6 Cool things you can do with Excel if() )

Here In this Example I like to add one more Column K with the Data “New” and “Old”, Maybe Column Heading will be “Cust Type”.

Now I need to have the Total Sales in Column G, with only “New” Customer of that City. How can I make this Condition,

Pls note , I don’t want to Filter the “Cust Type” /Column K
(I also think in the Range Filter will not work).

regards
Satish K
Mysore

Chandoo December 24, 2008

@CraigM: I hope you got your answer. Sorry for the delay in response. Whether you have 7 items or 70 items, using max(), min() and average() functions on the range will extract the maximum, minimum and average values for you. If you need median value, try the median() function. Since all these functions accept ranges as inputs, they are very easy to use and extend to tables.

@Satish: Welcome to PHD Blog. Thanks for asking your question.

Unfortunately sumif() in excel only accepts one condition range. That means we need some tweaks to get new customer sales per city. Here is how I would do it.

Add another column named type-city and use a formula like this to fill that column: = customer-type & “-” & city
so it will have values like New-Columbus, Old-Washington etc.

Now in the sumif() we will give this new column as condition range and for the condition value we just add “new-” before the city name we want the sum for. For eg. =sumif(type-city-list, “new-columbus”, sum-values) will tell us how much sales came from new customers in columbus city from the list.

Let me know if you have some difficulty implementing this.

Satish December 24, 2008

Thanks Chandoo
for the Work around, solun I will try implement it .
Happy Holiday

regards
Satish K

Asad July 2, 2009

Thank a lot for this.I appreciate your work..
God Bless You..!

GhOsT September 1, 2009

I have a problem and i hope you can help me. I need to create a if statement that will give me results. It is points for horse riding if the rider gets a 1ste place he must get 100 points and if he get 2de place he must get 95 points and so on. Can any one help me. Please E-mail me at leonsaunders20@gmail.com

Chandoo September 2, 2009

@Ghost : you can simply write a formula like =(21-a1)*5 assuming a1 has the place.

Andrea September 23, 2009

I have a whole stack of weighted averages that roll up to a series of overall tasks. I have figured out how to get the sum of the weighted averages – however I cannnot figure out how to get percentages for the top tier tasks…

An example to help explain:
1) Build the house
a) Foundation
i) Excavate the trench (weighting 30%) 36% complete
ii) formwork (weighting 25%) 25% complete
iii) rebar (weighting 15%) 17% complete
iv) pour concrete (weighting 30%) 0% complete

I know how to get the weighted average for the first level up (i.e. the foundation), but I am struggling to get the progress complete for the top level (i.e. Building the House) when I have a whole lot of groups such as the first level.

I hope I have clarified what I am asking for, and can you help??

SeanL November 2, 2009

Is there a straightforward way of extracting the nth biggest value from an unsorted list?

I tried large(A$:A$,n) but that gave me the nth value for the unsorted list (which would be perfect if my data was sorted, rather than unsorted).

Chandoo November 2, 2009

@Andrea… I am sorry, I have noticed this comment a bit too late. Are you still looking for a solution?

@SeanL: the large formula should work in your case. Can you post the data for us to see ?

SeanL November 3, 2009

Chandoo:
As it turns out, I should have been using small, not large, and the source of the problem is that I was wanting to apply the formulae specifially:
=SMALL(DataRange, TRUNC((COUNT(DataRange)*N$1)-(1.96*SQRT(COUNT(DataRange)*N$1*(1-N$1)))))
=SMALL(DataRange, TRUNC((COUNT(DataRange)*N$1)+(1.96*SQRT(COUNT(DataRange)*N$1*(1-N$1)))))

To a column on a table with a filter applied to it, and have the forumla ignore the hidden values, and only perform the operation on the filtered values – that way I don’t have to mess around with redefining the data range if I want to look at a slightly different data set (eg a different date range), or defining data ranges for several analytes across several sites.

scout November 6, 2009

I have a list where some people have paid and others not. I want to create a sheet that has only the people with “0″ dollars balance. Columns I am choosing from are name, rank, balance. These are unsorted (order cannot be changed). I would like the “Balance Due” sheet to choose which person has a non-0 balance and list them in another sheet with their name, rank, and balance.

I know I need to use a IF statement with the balance column 0 (not equal to 0), but I can’t figure exactly how to return the corresponding name and rank along with the balance. I am thinking I will need to nest a INDEX or MATCH in there somehow.

Thanks in advance for speedy reply!

Sessoms January 6, 2010

Here is my interesting IF statement question…

When using a long SUMPRODUCT as part of the IF statement condition, is there a way to avoid having to rewrite the SUMPRODUCT statement as one of the Values?

EX. IF(SUMPRODUCT(blah, blah)<100, "No Data", SUMPRODUCT(blah, blah))

I typically do arrays like this and the formulas get awfully difficult to read.
I wish I only had to write SUMPRODUCT statement one time and not repeat it.

Any ideas would be much appreciated! Thanks! I love your site!

Chandoo January 7, 2010

@Scout: I am sorry, but I couldnt get back to you any earlier. Did you find the answer? If not, let me know, I can help.

@Sessoms: Do you have Excel 2007? if so you can use IFERROR formula. Otherwise, the cheapest way is to find the parts of the formula that cause an error and just check for them in in the first part of IF. Another option and probably a cleaner one is to use a helper column. You can easily hide it or place it on a hidden sheet if you dont want your audience to see it.

Sessoms January 7, 2010

Thanks Chandoo. I did see the IFERROR formula and saw how it could be useful when I use those times I used IF(ISERROR(formula_goes_here), “”, formula_goes_here)

Unfortunately, when the condition is not to test for an error, I didn’t find anything that fit the bill. For clarity of the formula, I agree, the helper columns are an easy solution and one I will probably resort to in the future.

Thanks for taking the time!

Chandoo January 7, 2010

@Sessoms, if it is just a display thing, you can also use custom cell formatting with a code like this [<100]“No data”;0;

This will just show “No data” when the value is less than 100, but the value is still there in the cell. So if you pass the range to a chart or another formula they still get values less than 100.

Learn more about custom cell formats here: http://chandoo.org/wp/tag/custom-cell-formatting/

RSS feed for comments on this post. TrackBack URI

Leave a comment

   Name (required)

   E-mail (required, never displayed)

   URL


If you have a question, please ask in the forums

Recommended Excel, Charting, VBA books