Learn how to a build a cloud-first strategyRegister Now

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

# 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
• 2
2 Solutions

Commented:
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

Commented:
i.e.

Chris
demo-cs.xlsx
0

Commented:
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

IT & 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

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