Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Conditional Sum Across Multiple Worksheets

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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 33
Default Conditional Sum Across Multiple Worksheets

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.)

"Redline" wrote:

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.

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,180
Default Conditional Sum Across Multiple Worksheets

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.
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Conditional Sum Across Multiple Worksheets

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


"Herbert Seidenberg" wrote:

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.

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,441
Default Conditional Sum Across Multiple Worksheets

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


"Viridel" wrote in message
...
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to make a macro to clear multiple cells from multiple worksheets? [email protected] Excel Worksheet Functions 2 October 18th 07 04:31 PM
Applying Conditional Formatting to Multiple Worksheets - Excel 200 David Excel Discussion (Misc queries) 0 September 22nd 07 05:50 PM
Delete Blank Rows Code - Multiple Worksheets - Multiple Documents BenS Excel Discussion (Misc queries) 3 June 29th 07 12:20 AM
opening multiple files into one workbook, but multiple worksheets.... Andy Excel Discussion (Misc queries) 0 January 24th 07 07:34 PM
Conditional formulas between multiple worksheets Nathan Excel Worksheet Functions 1 August 5th 05 08:41 PM


All times are GMT +1. The time now is 04:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"