Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1313
  • Last Modified:

SumIf Function across multiple worksheets

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
0
Kyle Witter
Asked:
Kyle Witter
  • 2
2 Solutions
 
Chris BottomleyCommented:
Have you tried in B4:

=SUM(April:March!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.

Chris
0
 
Chris BottomleyCommented:
i.e.

Chris
demo-cs.xlsx
0
 
barry houdiniCommented:
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

=SUM(SUMIF(INDIRECT(TEXT(DATE(0,{1,2,3,4,5,6,7,8,9,10,11,12},1),"mmmm")&"!A4:A21"),A4,INDIRECT(TEXT(DATE(0,{1,2,3,4,5,6,7,8,9,10,11,12},1),"mmmm")&"!b4:b21")))

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
0
 
Rob HensonIT & Database AssistantCommented:
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.

Cheers
Rob H
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now