The SUMIFS Formula!

The SUMIFS formula is probably one of my favorite formula's for getting unchained and out of a jam.  Now this formula might scare some people off the bat, but really all you are doing is summing a column, but only if it meets certain criteria.

Lets take a simple example where we have hours worked by month and by employee:

Lets say that we want to capture the number of overtime (OT) hours worked for the month of January so that we can keep track of our hours to some budget we might have.  When you type in =SUMIFS excel will ask you for the sum_range and then your criteria_range1 and criteria1. 

I have created a little table that will show the summary of the hours for January in columns G and H

Since we are trying to calculate the number of OT hours, column C will be the sum_range.  But, now we need to create a condition where it only sum's the OT hours and not all hours.  For criteria_range1, we will select column D, since this separates what type of hour we are capturing.  For criteria1, we will select OT in our summary table (column G, row 4).  The formula will look like this:

Now, the only issue is that with the current formula, we are picking up all OT hours!  We just want a summary for January!  Garr... chained :( .  Well luckily for us we can put in multiple criteria!  We just need to add another comma in the formula to get a criteria_range2 and criteria2!  Since we want January only, our criteria_range2 will be column E, and our criteria 2 will be Jan in our summary table (column G, row 2).

And boom goes the dynamite! There, now you have it, a perfect little summary of how to calculate based on multiple criteria!  Now lets see if you can finish off the formula's to get a nifty little table like below.

Let's see you pull that one out next time you want to impress the boss!  unchained for now :)

until next time,
forever chained...

Welcome!

Welcome to the blog! This is the obliigtory first post welcome that everyone must do... I really just wanted to start this blog to document some formulas and different tricks that I have done with MS Excel to keep a memory of them, but then I though why not open it up to the world? I'm sure there are more of us out there who feel trapped and handcuffed when using Excel, so we can all share in the pain and gain with this blog. If you have problems you need help with leave a comment with some details and we can work it out together!

until next time,
forever chained...