Excel Min If and Max If Functions

Unfortunately, the only way to do this is with array formulas, which are pretty intimidating beasts. Here is a simple sumif function:

=sum(if(((a1=b1:b5)),1,0))

Then hit ctrl+shift+enter (CSE: some call these CSE formulas). See here for more on array formulas.

Have a look at the attached file (“array_formulas“). It looks for matches between a1 and whatever’s in b. It sums the values from the matching rows but in the c column. Hard to explain in words, have a look at the attached.

What’s cool is that you can do this with an unlimited number of criteria and use max or min. Like this:

=max(if(((a1=b1:b5)),c1:c5,0))

=min(if(((a1=b1:b5)),c1:c5))

Note with min, you can’t set the FALSE condition of the If statement to 0 because 0 will always be the min!

Next version is to set the multiple criteria using * signs:

=max(if(((a1=b1:b5)*(a1=d1:d5)),c1:c5,0))

And can set or conditions using a + sign:

=max(if(((a1=b1:b5)+(a1=d1:d5)),c1:c5,0))

What these are really doing is giving you some database functionality in excel. If you work with datasets smaller than 50,000 rows or so the pain of putting something into a database isn’t worth the extra speed for searching. And excel’s function system is much more powerful than SQL for even moderately advanced analysis.

Excel gives you database query functionality with the pivot tables, but I find these a pain to work with and usually just build my own pivots with array formulas.

One big note of caution: array formulas are super duper resource hogs. Eventually you’ll bog your system down in endless calculations if you use them too much. Which I do all the time.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s