The Power of the SUMIFS Function

Jim Hook

When I discovered the SUMIFS statement recently it solved a problem that had made me keep things like the project timesheets for our company in a very rigid format that basically listed all projects and functions (types of work) that could be charged to. I had wanted to let people select the project they were working on from a dropdown list, then select the function they were performing from another dropdown list and then enter their hours daily for the week by making entries for each day. The problem was that this results in an random sequence of project/functions on their project timesheets that I could not see how to consolidate across many projects functions and people to get the bottom line charges for each project and function for the week. The SUMIFS has changed all of that now. First I link in all the time charges from each person's timesheet into a single sheet creating what amounts to a weekly time charges database. Then I use SUMIFS functions to scan the database and organize the charges as needed to link out to a separate project tracking sheet for each project. This sheet then automatically links into our project financial tracking system. The result for the employees is a much shorter and simpler project timesheet. 

 

The SUMIFS function was a little tricky to understand at first but it works great once you have used it a couple of times. Basically, it allows you to sum any entries (numbers only) in a column where each row meets one or more criteria that you specify. So, I can write a formula in a cell that gathers all the charges on Project A for the Design function across all employees and then do the same for each additional project and function which gets everything in a format for linking out to the rest of the system. It works great!

Greg Hawk

Great read! Formulas are powerful once you get the hang of them. They are a great way to take a good sheet and turn it into an AWESOME sheet