SumIf across multiple worksheets

I have 13 worksheets I want to sum quarterly.  The formula I've been trying 
is: 

=SUMIF(A5:A36,"136982",'Apr 4:Jun 27'!G5:G36) but I get a #Value! error 
message.

In column A I have contract numbers.  In column G is the crew size.  I want 
to sum all the crew size based on specific contract numbers.

Any help is appreciated :)
0
Utf
1/18/2010 6:22:02 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
2017 Views

Similar Articles

[PageSpeed] 4

In each of the other worksheet, are the contact numbers also in column A?
best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Alberta Rose" <AlbertaRose@discussions.microsoft.com> wrote in message 
news:A12020BB-47D1-40B4-9E79-419DE7AE3D53@microsoft.com...
> I have 13 worksheets I want to sum quarterly.  The formula I've been 
> trying
> is:
>
> =SUMIF(A5:A36,"136982",'Apr 4:Jun 27'!G5:G36) but I get a #Value! error
> message.
>
> In column A I have contract numbers.  In column G is the crew size.  I 
> want
> to sum all the crew size based on specific contract numbers.
>
> Any help is appreciated :) 

0
Bernard
1/18/2010 7:51:01 PM
Put the 13 sheet names in M1:m13 and use#

=SUMIF(INDIRECT("'"&M1:M13&"'!A5:A36"),"136982",INDIRECT("'"&M1:M13&"'!G5:G36"))

---
HTH

Bob Phillips

"Alberta Rose" <AlbertaRose@discussions.microsoft.com> wrote in message 
news:A12020BB-47D1-40B4-9E79-419DE7AE3D53@microsoft.com...
>I have 13 worksheets I want to sum quarterly.  The formula I've been trying
> is:
>
> =SUMIF(A5:A36,"136982",'Apr 4:Jun 27'!G5:G36) but I get a #Value! error
> message.
>
> In column A I have contract numbers.  In column G is the crew size.  I 
> want
> to sum all the crew size based on specific contract numbers.
>
> Any help is appreciated :) 


0
Bob
1/18/2010 7:58:22 PM
Reply:

Similar Artilces:

sumif formula #2
At the moment I am trying to work a sumif formula The problem I am encountering is once I have set the range and criteri it won't pick up the sum range as the range I am specifying is fo example J15:CB45 - it will pick up J15:CB15 but I need it to pick u the whole range - do you know of anyway I can resolve this. My formula is =sumif($J$3:$CB$3,E$3,($J15:$CB45)) but it doesn't see to work this one does though =sumif($J$3:$CB$3,E$3,($J15:$CB15)) bu that doesn't help me! I have put a print screen of the spreadsheet I am trying to work on. Thanks Jenni -- Message posted from htt...

link drop down menu to info in worksheet
want to create a drop down that shows information from other worksheets. ie as i select A it will read the information on A from the worksheet2. How about a 'list'? Position your cursor where you want it in worksheet 1. Go to Data, Validation, Settings, Allow and choose List. In Source, click on the red square in the spreadsheet icon, go to worksheet 2 and select what you want displayed in worksheet 1. Click on the red again, and OK. When your cursor enters the cell in worksheet 1, a downarrow appears with the list you linked to. HTH, Carole O "bob" wrote: >...

sumif across multiple sheets in excel 2007 based on a condition
I have a small work book, tracks deliveries made and trucks used. I have seven sheets Saturday thru Friday and one sheet that totals everything up! The weekly sheets have 34 rows representing 34 stores that we service and the total sheet also has 34 rows that contain the totals for that week. Here is my question I am trying to, on the totals sheet, sum up the total trucks that we used per store per week. In the cell would be entered 0, 0.5, 1, 2 and so on depending on how many trucks we used for that day. Yes we use 0.5 if the store was combo'ed with another store. I ne...

how to run multiple macro's at once
Hello, I have 350 separate macro's, how do I get them to run in successive order, other then having to click on each individually? Is there a way to 'link' them all together? Thanks, Roxane You can write a macro that in turn calls the other macros. E.g., Sub RunThemAll() Macro1 Macro2 '... End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Roxane" <Roxane@discussions.microsoft.com> wrote in message news:44732365-A840-4C5D-9025-D4B90AEEB887@microsoft.com... > Hello, > > ...

Multiple date select calender
I need a calender for MFC in which i can select multiple dates similar to what is there in calender of outlook 2003. Also I need the functionality to be able to select the days i.e. sunday, monday, etc. Is there some activex available freely which i can use. Or is it possible to achieve the same by subclassing the existing CMonthCalCtrl in MFC. Please guide me. Thanks in Anticipation Software Engineer wrote: > I need a calender for MFC in which i can select multiple dates similar to > what is there in calender of outlook 2003. Also I need the functionality to > be able to sel...

Multiple reply templates
I am trying to create multiple templates to be used to reply to differing e-mails. I created my first template and then used my rules setup to automatically use it to reply to a received e-mail. I then thought that I created three additional templates for three other rules, but when I try to set up the rules, the new templates do not show up. Anyone have an idea why this might be happening? Where did you save the template files? Or are these "have server reply" rules? -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrato...

SUMIF or SUMIFS
Here is what I have Col A Col B Col C 1310 3 3,463.00 1315 3 740 1330 3 1369 3 -178 1375 3 -105 1640 3 110 135 4 1310 4 1,460.00 1315 4 1,521.00 1375 4 -65 1310 6 3,284.96 I am trying to figure out a way to add column C to a new cell if Column A is between 1310 and 1369. Any suggestions?? =if(and(A1>1310;A1<1369);C1;"") HTH "Jeff" wrote: > Here is what I have > > > Col A Col B Col C > 1310 3 3,463.00 > 1315 3 740 > 1330 3 > 1369 3 -178 > 1375 3 -105 > 1640 3 110 > 135 4 > 1310 4 1,460.00 >...

Help!larger than A4 brochure that folds in half & across 3 times
I want to be able to fold the paper in half and then proceed with a standard three column brochure that would have information on all sides. I cannot find and template in publisher. Not sure what you are trying to do. Setup your page as a booklet, in the arrange menu setup 3 columns. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Aine Mc Laughlin" <AineMcLaughlin@discussions.microsoft.com> wrote in message news:D2FACA3F-D569-445A-AC97-85371675F3CE@microsoft.com... >I want to be able to fold the paper in half ...

Opening excel 2000 worksheets in office 97
if you send an excel 2000 archive to someone who has office 97, could this person open it? I have 97 and 2000 on the same machine and have no problems. There may be problems with macros & formulas if they contain update commands not in 97 -- Message posted from http://www.ExcelForum.com If an "archive" is a file/workbook, no problem. XL97, 2000, 2002 and 2003 have the same file format. Gord Dibben Excel MVP On Tue, 22 Jun 2004 21:45:01 -0700, Ni! <Ni!@discussions.microsoft.com> wrote: >if you send an excel 2000 archive to someone who has office 97, could this pe...

SUMIF or SUMIFS help
I have 2 sheets in one workbook (Sheet 1 and Sheet 2) Sheet 2 has 3 columns: A B C MAKE TYPE QTY 1 toyota compact 10 2 ford pickup 15 3 toyota sedan 20 4 toyota pickup 80 5 nissan hybrid 10 Sheet 1 has 2 columns: A B MAKE PICKUPS 1 toyota ? (SUM) I need Sheet 1,B1 to calculate the total number of matching items in sheet 2 that matches the data entered in Sheet 1,A1. In other words, I need sheet 1,B1 to automatically sum up the total number of to...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

Formula to copy multiple cells onto another sheet
I have a worksheet containing names and address that are repeated onto other sheets based on criteria in another column. What formula if any can I use to determine if that person meets that certain criteria to automatically copy the 3 columns that the name, phone, and address are located in to another sheet? A little more info: I have multiple worksheets and one master list containing 6 columns: First, Last, Birthday,Phone, and Address, and class. In the class column is a variety of class names. I would like to write a macro (I have never done this before!) ,if possible, that will ...

another sumifs plea
I'm struggling to convert a sumifs line from 2007 to excel 2003. The line I have working correctly in 2007 is: =SUMIFS('Washing MC Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"= 0.00",'Washing MC Rental'!$L$4:$L$21,">28/2/2010")-SUMIFS('Washing MC Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"= 0.00",'Washing MC Rental'!$L$4:$L$21,">31/3/2010") Can any one please help me to put this into 2003? Best Regards Dave =SUMPRODUCT('Washing MC Rental'!$K$4:$K$21,...

Need formula to add specific #'s across the top
I want to be able number columns across the top of my excel sheet. EX. (0, 0) (1, 0) (2, 0) How do I get it to keep adding 1 number to the front # and keep the second # as a zero? When I do it down a column it works fine but when I try to do it across it just keeps repeating itself so that I get: (0, 0) (1, 0) (2, 0) (0, 0) (1, 0) (2, 0) instead of (0, 0) (1, 0) (2, 0) (3, 0) (4, 0) (5, 0) Any help would be greatly appreciated. Enter in A1 ="(" & COLUMN()-1 & ", 0)" returns (0, 0) (1, 0) (2, 0) (3, 0) (4, 0) (5, 0) as it is copied across row 1 Gord Dibben ...

Deleting 1 account when multiple at institution
This is related to Money 2005. (FWIW, I'm sorry I upgraded. Several years ago Money was a great product. Over the past couple of annual upgrades more things are broken than work correctly. I'm a MS advocate, but they should be ashamed of this product.) At any rate... In times past I had 3 accounts with Citibank. 1 of them was closed several years ago. On Citibank's web interface this old account still shows up. When I had Money 2005 connect to Citibank for online banking, it downloaded information on 3 accounts--1 of which no longer exists. I tried to delete this ac...

Create a formula in a worksheet that links two other worksheets
I am working with 3 worksheets in the same workbook. My worksheet tabs are labeled: 9C moinfo slsbudg I am trying to create a formula in the cell for worksheet tab 9C that links the two other worksheets. In words, this is what I need the formula to do: If moinfo cell F5 =1, then slsbudg cell C32=9C cell F19 Any help is greatly appreciated. Thanks. ...

Multiple mfc activex controls in one dll
In VS6, there was a possibility to define up to 4 controls, when creating a new activex project. No such possibility in VS2005. Is there any way in 2005 to add an addtional one to an existing MFC ActiveX project? Any pointers will be appreciated! ...

Multiple signatures for multiple mail accounts
Hi all I posted the question below on the OE6 newsgroup but came to the conclusion that OE6 cant really handle this very well so I thought I would ask the question here I'm trying to setup different signatures for multiple different accounts. I've setup 10 accounts and 10 signature files However when I start a new message it puts in the signature from the default account. If I then straight away change the "From" account......The signature does NOT change ? Surely if you can set different signatures for different accounts on the signatures tab in the tools menu .........

sumif vs sumproduct question
Hi, I have a simple spreadsheet with column B containing all dates, and column D containing all numerical values. The dates range throughout a year. At the bottom of the sheet I've entered January through December in another column. To the immediate right of the month named, I have a formula which gives me the total for that month. For instance, next to January my formula reads: =sumproduct(--(month(b6:b370)=1),d6:d370) this formula works, but it seems to me I should be able to use SUMIF too. I've tried =sumif(d6:d370,month(b6:b370)=1,d6:d370) but that just returns a blank ce...

SUMIF function?
I'm tring to get excel to do a conditional sum based on the following. I have two columns, the first containing a reference number, and th second an ammount which is either positive or negative. In another sheet I have the reference number and beside it i want cell to calculte the sum of all the sells with that ref number and tha contain a positive amount, see the example below. Ref # Ammount 1 -5.15 1 6.00 1 -3,50 2 2.20 2 -3.40 2 2.40 3 6.40 3 -7.20 3 -1.80 SO then on another sheet i have Ref Positive Total Negativ...

Custom View: Keeping worksheet hidden?
(Excel 2003) I've got a workbook with several worksheets (some hidden). I'd like to use "custom views" to basically set the print/page setup for each worksheet---the setup varies per sheet. Custom view allows me to do this, but it also unhides the worksheets. Question: Is there anyway to keep a Custom View from hidding/unhiding a worksheet? I tried saving the view with the sheet hidden and unhidden, but when I select the view, it shows everything as the view was saved. Problem is, the worksheets that are hidden are unhidden by other events....the custom view is interfering ...

Receiving multiple copies of messages
I installed Outlook Live several days ago. I have not been using Microsoft Outlook fro quite some time. For the first few days, my messages were downloading normally. All of a sudden, Outlook has started downloading multiple copies of the same message. I do not have any filters set up, so this cannot be a filter issue. I have 4 pop accounts set up, 1 MAPI account (Hotmail) and 1 IMAP account. The majority of my mail is received from my Hotmail account. The pop accounts are all set up with the option to leave messages on the server until I empty the deleted items folder. My IMAP...

Need calendar displayed seven across
I cannot remember how to do this. I keep trying to figure it out and I just cannot find the answer. I am running outlook 2002 with no email profile. Just installed on new vista computer. I want the calendar view seven days across. It currently displays calendar six across with Saturday Sunday at the end in a split box. How do I get it to display seven across? Thanks for the help Bob disable compress weekends. Right click on the calendar grid and choose other options to find it. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Com...

SUMIF and a criteria range
Hi, I would like to use the SUMIF statement for summarizing when the criteria is a range and not a fixed value. Example: I have entered several invoices in column A, Arrival date in column B. On a different sheet I would like to summarize the invoices that have invoicedate between the first of and the last date of the month. Can this be done with SUMIF or is there a another way to fix this? Best regards Fredde Pretty simple. Did you try before asking? =SUMIF(A:A,G15,C:C) -- Don Guillett SalesAid Software donaldb@281.com "Fredde" <oleander@rocketmail.com> wrote in messa...

Printing charts across more than 1 page
I would like to know if it is possible to print 1 chart across more than 1 page, as the chart I'm working on has 500 items on the X axis. I have tried to paste it into excel and expand it from there but I can't get it to print out. Steph - I think I would try to make 5 charts with 100 items each, or some similar break down. At least that way, each page gets a Y axis. You could embed the chart in a worksheet (right click the chart, choose Location from the popup menu, check Object, and pick the sheet from the list). Then change to Page Break preview, and stretch your chart t...