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.
Not Unreasonable 