Conditional Sum Across Multiple Worksheets

[Redline]

I am trying to conditionally sum values across multiple worksheets and cannot
seem to get it to work. Basically I have 130 different worksheets each
representing a day. Each day has multiple job codes (only 8 different codes
are used throughout though) and I want to sum the work hours associated with
each job code. The number of rows also varies, some days have 2 entries and
some have 20 and I think that is what is screwing up my attempt to use
arrays. Any help would be most appreciated, previous formula attempts below,
have a good laugh!

={SUMIF('5-28:5-29'!I1:I100,Test!A1,'5-28:5-29'!F1:F100)}

Using only 2 pages as an example, the job codes are in column I on every
sheet and work hours are in column F.

 

[J. Sperry]

The Excel 2007 help file ("Create a 3-D reference to the same cell range on
multiple worksheets") includes a list of functions that can handle the 3-D
reference (as they call it). Neither SUMIF nor IF is one the list.

(By the way, thanks for the question. I didn't even know about this feature.)

 

[Herbert Seidenberg]

But there is a workaround.
Give your two dates defined names:
Start
5/28/2007
End
6/1/2007
The data in I and F are named JobCode
and WorkHr respectively.

=SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT
(ROW(INDIRECT(Start&":"&End)),"m-d")&"'!JobCode"),
A1,INDIRECT("'"&TEXT
(ROW(INDIRECT(Start&":"&End)),"m-d")&"'!WorkHr")))

The formula can be simplified if your sheet tabs include
simple sequential numbers as names.

 

[Viridel]

Sorry but I can't make heads or tails of the formula provided below. The
INDIRECT and & stuff is throwing me.

I have a similar issue, where I keep stats for a league, and pull Goals,
Assists & Shots from a Game Sheet. Each Game Sheet has it's own Tab. The
player is ALWAYS listed in Column J, so I need a COUNTIF to do a Game Count
(if a player played in a game), as well as SUMIFs for Goals (K), Assists (L)
and Shots (M). There are goalie stats as well, but once I get a formula that
works (that I can wrap my head around [;-), I'll be able to adapt as needed.

My "work in progress" formula is:
=SUMPRODUCT(SUMIF('1:2'!J:J,A2,'1:2'!K:K))

Seemingly very simple, and works flawlessly on one tab, but it #Value's out
when I change '1' to '1:2'

- J

 

[Bernie Deitrick]

J.,

SUMPRODUCT is not 3D range compatible.

The best work around is to use only one sheet for all games - usually, adding one column to sheet
template for entry of identifier information (think about how you change the tab name to identify
games) is enough. Then simply filter your data set to show only the data that you are interested in
(by game) and your one sheet will look exactly like your many individual sheets. And your formulas
will work.

HTH,
Bernie
MS Excel MVP

 

[mlshield]

I am currently suffering similar problems with 3D references.

I have three sheets AA, BB, & CC, and I wish to sum the same cell (A1) across all of them. The following works just fine:

=SUM(AA:CC!A1)

Now I wish to be able to use an indirect reference to specify the worksheet range, for example I wish the user to type AA in cell A2 and CC in A3 (just in case the user wishes to sum across sheets AA to BB they can then just change the cell entry in A3 rather than the formula), so the formula would look like:

=SUM(INDIRECT(CONCATENATE(INDIRECT("A2"),":",INDIRECT("A3"),"!","A1")))

but all I get is a zero sum answer, regardless of the values I enter in cell A1 for sheets AA through CC.

Any advice would be very warmly received, I'm losing a lot of time and sleep to this!!

Cheers,

-M