Smallif

I just learned a handy Excel trick to create just about any type of conditional formula. For context, right now Excel offers pre-built formulas that lets you sum up an array given a specific criteria (sum up all of column B whenever its value in Column A = Texas). Excel calls this "sumif." But what if you want to find the minimum, maximum, or rank given a specific criteria?

• First, check out this website.
• Then check out this clever "smallif" example: I have 10 people scattered throughout column A (100 times). Each person has several different values listed in column B. I want to rank the values for each person from smallest to largest. Try this: {=SMALL(IF($A$1:$A$100="John",$B$1:$B$100),1)}. Note that the brackets {} are automatically inserted by pressing CNTRL+SHIFT+ENTER at the end of your formula. Repeat this formula as many times as you need, entering ,2, 3, 4, 5, ..., n for as many values as you have (in order to rank them from 1 to n)