SumIf Function across multiple worksheets

Posted on 2011-05-04
Last Modified: 2012-06-27
I'm working on a spreadsheet that simply calculates totals across multiple worksheets.  The tricky part is on the second worksheet (Attached).  I'm trying to calculate all of the total goals for an employee over the course of a years span (april-march).  The difficulty comes that I need criteria to validate a line of an employee, that way if for some reason they're not on the list of employees for a month, then the counts are still accurate.  Is there a good way to do this?  

Before this particular layout, I did it all in one worksheet, and used a simple SUMIF function to find the data.  =SUMIF($A$4:$A$303,"=Employee 1",B$4:B$302).  Now that I have it in multiple sheets (so that it prints and displays better), I'm having difficulty with that calculation. demo-cs.xlsx
Question by:Kyle Witter
    LVL 59

    Assisted Solution

    by:Chris Bottomley
    Have you tried in B4:


    And then drag it down followed by drag the column across.  It can be made more complex using lookups but it seems straightforward to me in this way.

    LVL 59

    Expert Comment

    by:Chris Bottomley

    LVL 50

    Accepted Solution

    Assuming you always have sheets January to December you can use a formula like this to SUMIF across the same ranges in all 12 sheets


    Of course if the employess are always listed once each and in the same order as per you example then Chris's solution will be sufficient....and simpler

    regards, barry
    LVL 31

    Expert Comment

    by:Rob Henson
    Alternatively, turning it on its head.

    Leave all the data on one sheet, have the sumif calcs all on one sheet and then run an advanced filter by month to export to separate sheets for printing.

    Or summarise in a pivot table with the page breaks option selected.

    Rob H

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
    The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now