If you’re in a position where excel has slowly become a tool, or really a programming api in itself, you are likely about as excited about hitting the save or calculate buttons as you would be to hear your dog ate your ipad.

Likely, you’ve trolled googled and are well versed in VLOOKUP, Sumif, Sumproduct, Array formulas, and countifs..

If you have excel 2007 and up, there is a light at the end of the tunnel. Excel 2007+ contains some new functions, including SUMIFS and COUNTIFS!

Remember why you had to use array formulas (that look like {=(Sum… )to begin with? Because countif on it’s own couldn’t handle multiple conditions.

So if you do counts or sums across values and want multiple conditions Replace your array formulas sums and counts with SUMIFS and COUNTIFS

Ex: Sum all values in ColA where ColB=Georgia and ColC=Yes

translates to =SUMIFS(A:A,B:B,“Georgia”,C:C,“Yes”)

Ex2: count all occurences where ColB=Pennsylvania and ColD=Booked

translates to =COUNTIFS(B:B,“Pennsylvania”,D:D,“Booked”)

Simple huh?

It get’s better…you can get rid of those pesky named ranges you had to create for your array formulas to work..

You’re welcome.

Credit my awareness of these new functions to http://msdn.microsoft.com/en-us/library/aa730921.aspx

Ok OK I’m not done yet..

So you hate those #N/A’s but even more you hate typing out..if(ISNA(Vlookup……

I do too.

You can start using the IFERROR function instead of IF(ISNA(vlookup in some cases!!

You do not need to set a true condition for IFERROR, only a false condition.

The true condition will return the value of the formula.

Ex.

If(ISNA(Vlookup(G1,‘OtherSheet’!D:D,1,FALSE)),“No”,Vlookup(G1,'OtherSheet’!D:D,1,FALSE))

changes to

Iferror(Vlookup(G1,'OtherSheet’!D:D,1,FALSE),“No”)