Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-14-2012, 12:23 PM
Berk21 Berk21 is offline sumproduct formula to pull info from multiple sheets Windows XP sumproduct formula to pull info from multiple sheets Office 2010 64bit
Novice
sumproduct formula to pull info from multiple sheets
 
Join Date: Jan 2012
Posts: 11
Berk21 is on a distinguished road
Default sumproduct formula to pull info from multiple sheets

Im having a hard time get the formula correct if its even possible.



I have a Win Loss Spread sheet and im trying to summerize it on one sheet by statistics through out a 17 week period.

each sheet is a week..week 1,2,3,4..so on.
I can summerize one week at a time but not all the sheets progressively.

If i try to add all the weekly tabs, with this formula, it gives me an error..
=SUMPRODUCT(--('Week 1 :[Week 17]Week 17'!C$9:$C$18="Casey"),--('Week 1 :[Week 17]Week 17'!I$9:$I$18="L"))

If I place this in for only one week tab it works fine.
=SUMPRODUCT(--('Week 1 '!$C$9:$C$18="Casey"),--('Week 1 '!$H$9:$H$18="L"))

anyone have any ideas or a simpler way?

Thanks!!!
Reply With Quote
  #2  
Old 01-14-2012, 01:23 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline sumproduct formula to pull info from multiple sheets Windows XP sumproduct formula to pull info from multiple sheets Office 2003
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Hi
I don't think 3 D references work with SUMPRODUCT

Maybe create a formula in the same cell in each sheet and add the results with the SUM function which allows for 3 D
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 01-14-2012, 01:42 PM
Berk21 Berk21 is offline sumproduct formula to pull info from multiple sheets Windows XP sumproduct formula to pull info from multiple sheets Office 2010 64bit
Novice
sumproduct formula to pull info from multiple sheets
 
Join Date: Jan 2012
Posts: 11
Berk21 is on a distinguished road
Default

Pecoflyer,

Small issue. Each person will have to have totals on each sheet with 5 potential players and maybe 4-5 stats a piece...that would take up alot of space on each sheet..unless I hide the cells i guess.

Can i give you my example? you may see something easier by seeing it.
Thanks ahead of time.

Last edited by Berk21; 01-14-2012 at 01:42 PM. Reason: typo
Reply With Quote
  #4  
Old 01-14-2012, 05:07 PM
Berk21 Berk21 is offline sumproduct formula to pull info from multiple sheets Windows XP sumproduct formula to pull info from multiple sheets Office 2010 64bit
Novice
sumproduct formula to pull info from multiple sheets
 
Join Date: Jan 2012
Posts: 11
Berk21 is on a distinguished road
Default

Well, now I have no idea what I did...this is now more confusing to place all the weeks in one sheet..not only that, my formulas are all wrong and I cant figure them out....

What are the chances someone can look at this and help me out...maybe if i just tell someone where i want the information to show up, the formulas will be easy to copy and paste.

Thanks
Reply With Quote
  #5  
Old 01-15-2012, 01:44 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline sumproduct formula to pull info from multiple sheets Windows XP sumproduct formula to pull info from multiple sheets Office 2003
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Could you possibly post a small sample sheet describing " before" and "after" ?
( to post a sheet click edit on a post - Go Advanced - Click the paperclip)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #6  
Old 01-15-2012, 04:45 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline sumproduct formula to pull info from multiple sheets Windows XP sumproduct formula to pull info from multiple sheets Office 2003
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Cross post

http://www.excelforum.com/excel-gene...97#post2681897

and

http://www.mrexcel.com/forum/showthread.php?t=605405


Read this about cross posting
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #7  
Old 01-15-2012, 09:58 AM
Berk21 Berk21 is offline sumproduct formula to pull info from multiple sheets Windows XP sumproduct formula to pull info from multiple sheets Office 2010 64bit
Novice
sumproduct formula to pull info from multiple sheets
 
Join Date: Jan 2012
Posts: 11
Berk21 is on a distinguished road
Default

Well, now i know all the forums are linked..didnt know that..figured as I was working on the spread sheet and i hit a road block and didnt get an answer on the other 2 for 2 days, so i started this one.....so sorry to triple post.

let me get the two copies up so you can see what i started and what i attemped to change.

I would love to keep the APA file..and dispose of the APA2...once i know how to pull the data according to name, i can start tweeking the look again.

Thanks again, and also sorry.
Attached Files
File Type: xlsx APA.xlsx (60.0 KB, 9 views)
File Type: xlsx APA2.xlsx (46.2 KB, 7 views)
Reply With Quote
  #8  
Old 01-15-2012, 11:41 AM
Berk21 Berk21 is offline sumproduct formula to pull info from multiple sheets Windows XP sumproduct formula to pull info from multiple sheets Office 2010 64bit
Novice
sumproduct formula to pull info from multiple sheets
 
Join Date: Jan 2012
Posts: 11
Berk21 is on a distinguished road
Default

here is the after results i was planning on getting progressivly throughout the weeks....

Is it possible??? like i stated I was able to pull from one tab but not progressively..
and I couldnt tell the program to only count in the data is playing "our team"=Casey,Kaleena,Rob,Brian,Billy,Laurel.

I
Attached Files
File Type: xlsx APA after.xlsx (59.4 KB, 10 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
sumproduct formula to pull info from multiple sheets form a database from multiple sheets udea Excel 1 04-25-2011 02:34 AM
sumproduct formula to pull info from multiple sheets macro to pull data from multiple files psrs0810 Excel 2 10-25-2010 01:49 PM
sumproduct formula to pull info from multiple sheets How do I merge data from one sheet in a workbook out into multiple sheets nolesca Excel 4 06-07-2010 08:13 AM
Print Multiple Sheets Double-Sided flambe99 Excel 1 03-12-2010 01:15 PM
Excel 2007 doesn't print color graphics on multiple sheets mwoolf Excel 1 06-19-2009 02:34 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:29 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft