Total a column from sheet 2 based on value in sheet 1

Col B			Col C					 Col F
Mary             		Team 1				 $331.00 
George  		Team 1				 $222.00 
Sam 			Team 2				 $186.00
Tom			Team 2		   	             $100.00

Above is an example of my data on Sheet 2. On Sheet 1, I want to total all 
the total funds raised per Team shown on Sheet 2. I am trying to create a 
summary of what each team raised.

I tried using the formula: =SUM(('sheet 2'!F2:F482=Sheet1!B2)) and I get 
just a dash in my total col. 

Can anyone provide some help?

Thanks

0
5/16/2008 6:32:01 PM
excel 39879 articles. 2 followers. Follow

3 Replies
578 Views

Similar Articles

[PageSpeed] 29

Look in HELP for the SUMIF() function

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"NeedHelpFast" <NeedHelpFast@discussions.microsoft.com> wrote in message 
news:DE609634-7272-4E0C-8316-F654E0E15699@microsoft.com...
| Col B Col C Col F
| Mary             Team 1 $331.00
| George  Team 1 $222.00
| Sam Team 2 $186.00
| Tom Team 2                 $100.00
|
| Above is an example of my data on Sheet 2. On Sheet 1, I want to total all
| the total funds raised per Team shown on Sheet 2. I am trying to create a
| summary of what each team raised.
|
| I tried using the formula: =SUM(('sheet 2'!F2:F482=Sheet1!B2)) and I get
| just a dash in my total col.
|
| Can anyone provide some help?
|
| Thanks
| 


0
nicolaus (2022)
5/16/2008 6:41:18 PM
I had tried using help but did not find the answer. I found an old Excel book 
by Que and found the formula that I need.
0
5/16/2008 7:22:26 PM
Hi,

Your formula should look more like:

Assuming that you list  team names in column B of sheet1 and the team names 
are in column B of sheet2 also

=SUM(('sheet 2'!B$2:B$482=Sheet1!B2)*('sheet 2'!F$2:F$482))   this formula 
would be array entered (Shift+Ctrl+Enter)  or

=SUMPRODUCT(('sheet 2'!B$2:B$482=Sheet1!B2)*('sheet 2'!F$2:F$482))   not 
array entered.  There are other variations of this.

Cheers,
Shane Devenshire
Microsoft Excel MVP

"NeedHelpFast" <NeedHelpFast@discussions.microsoft.com> wrote in message 
news:DE609634-7272-4E0C-8316-F654E0E15699@microsoft.com...
> Col B Col C Col F
> Mary             Team 1 $331.00
> George  Team 1 $222.00
> Sam Team 2 $186.00
> Tom Team 2                 $100.00
>
> Above is an example of my data on Sheet 2. On Sheet 1, I want to total all
> the total funds raised per Team shown on Sheet 2. I am trying to create a
> summary of what each team raised.
>
> I tried using the formula: =SUM(('sheet 2'!F2:F482=Sheet1!B2)) and I get
> just a dash in my total col.
>
> Can anyone provide some help?
>
> Thanks
> 
0
5/18/2008 1:13:23 AM
Reply:

Similar Artilces:

An XML question
I would like to produce the following output based on my XML file: My Album (2005) Elapsed Time (hh:mm:ss): 00:07:00 Song 1: title1 Length (hh:mm:ss): 00:02:30 Song 2: title2 Length (hh:mm:ss): 00:02:15 Song 3: title3 Length (hh:mm:ss): 00:02:15 ===== <album> <general> <title>My Album</title> <year>2005</year> </general> <content> <song> <songTitle>title1</songTitle> <songLengthInSeconds>150</songLengthInSeconds> </song> <song> <...

Moving a value in Column D
I have a spreadsheet with values in a couple of cells in Column E. The rest of the cells are blank. I would like a macro that would move the value of any cell containing data (non empty cells) four cells backwards and two cells downwards to Column A. Is there a macro that would do this. -- Message posted via http://www.officekb.com Sub MoveIt() Dim rCell As Range For Each rCell In Columns("E") _ .SpecialCells(xlCellTypeConstants) rCell.Offset(2, -4).Value = rCell.Value rCell.ClearContents Next rCell End Sub HTH -- AP "mohd21uk via OfficeKB.com" <u20517@u...

Outlook (Office XP) #2
I am running Windows Xp. I have just installed office Xp on my computer and when I try to close down outlook i get an ERROR window come up that say Outlook has to shut down. CAN ANYONE HELP ME OUT. AppName: outlook.exe AppVer: 10.0.2616.0 ModName: msmapi32.dll ModVer: 10.0.2625.0 Offset: 0001e85d Thanks! ...

Limiting a drop down list to a value in another field
Hello, I have an Access database which lists certain software. There are two fields; the software company, i.e. Microsoft, and then the name of the software itself,i.e. Access. I want the name of the software to be limited by the company name, so the second drop down would not list, say, Adobe Acrobat. Is there a way to do this? Many thanks in advance. Joanne wrote: >I have an Access database which lists certain software. There are two >fields; the software company, i.e. Microsoft, and then the name of the >software itself,i.e. Access. I want the name of the softwa...

Totals on Sales Inquiry native windows
We would like to have the totals on the Sales Inquiries: Transaction by Customer Transaction by Document Sales Documents Sales Items Today we can retrieve this information in separated inquiries and reports, be we would like to heve a easier way to see this totals... The same suggest can be applied to Smartlist. If possible, you can create a tool to sum a field after you make a filter I created a screen where you can see all sales, separated by day of week, and week of year, and totals YTD,MTD and WTD, but I am not able to have the same information grouped by item number, for example.....

POP message #2
The cable company I use does not support Outlook. I keep getting POP. messages to put in my name and password every minute or so. Cable company says I have to use Outlook Express. How do I turn off Outlook and make Express dominant. Keep getting errors when I attempt to switch over and mail keeps going to Outlook What version of Outlook do you have? What is the exact text of the error message you're getting? Outlook will work fine for receiving mail from your cable company - it's just that the company doesn't want to go to the trouble of telling you how to configure it p...

Unicode Basic Latin and Latin-1 coverage of Western Europe
What gaps would there be in the coverage of Western European languages that would not be included in Unicode Basic Latin and Latin-1 ? "Peter Olcott" <NoSpam@SeeScreen.com> wrote in message news:vCr_i.2768$gd3.2472@newsfe18.lga... > What gaps would there be in the coverage of Western European languages > that would not be included in Unicode Basic Latin and Latin-1 ? The Greek alphabet. Euro currency. "Norman Diamond" <ndiamond@community.nospam> wrote in message news:e0KsXwmJIHA.4712@TK2MSFTNGP04.phx.gbl... > > "Peter Olcott"...

Re: fuction to copy Column a and b to comment or colum c?
Please help!!! I would like to know a fuction or a way to just recop what ever is in colum a and c to comment in colum d. Is this eve possible? If not maybe a fuction to put values of colum a and c t colum d. Please keep in mind this would prevent me from typing again. Thanks!!! -- Message posted from http://www.ExcelForum.com You can concatenate the values in A & C in column D with a formula: =A1&C1 or =A1&" "&C1 if you want those values in a comment, you'll need a little code. This is a sub (not a function): Option Explicit Sub MakeComments() Dim...

IF help #2
i have to calulate a Excel Spread sheet on a weekly basis so i know how to pay staff for product sales the file is output from a weird old dos program into a excel document I have 2 sheet inside a Excel document lets call them Sheet 1 and sheet 2 Sheet 1 has 3 columums Date Product and $Due etc 8/06/06 TABLE 8/06/06 CHAIR 8/06/06 TABLE $due Columum is not filled and i need this to auto fill Sheet 2 has a list of products in Columum 1 and 2 has the price on it PRODUCT $DUE Table $50 I need to do a if statement that if sheet 1 PRODUCT = SHEET 2 PRODUCT THEN sh...

working with multiple worksheets #2
I would like to collect data from one specific cell (cell E3) in 90 worksheets and have this data placed in one column from 1-90 on one single worksheet. Is there a way to do this without doing it one by one? Hi! Are you using the default sheet names? (probably not, huh?) If there is some common sequence in the sheet names then a formula could probably be crafted to do this. Biff "Shaun" <Shaun@discussions.microsoft.com> wrote in message news:B8CEFE1A-1B25-41F9-B72F-103CB6AF476D@microsoft.com... >I would like to collect data from one specific cell (cell E3) in 90 &g...

total cost on report
I have areport with a cost field that I would like to total that collum at the end of the report. How do I do this ? do I need to create another field (total) please help thanks, jettro Jettron wrote: >I have areport with a cost field that I would like to total that collum at >the end of the report. How do I do this ? do I need to create another field >(total) The usual way is to use a text box with an expression like: =Sum([cost field]) -- Marsh MVP [MS Access] I'm in design view of the report, where do I insert this expression? I clicked on page footer to insert thi...

Setting ID# to 1 & Total on bottom of query result
Hi, I have 2 separate questions. First how do I set the ID# to 1 for the first record. I copied my whole database including queries and forms which were 2009 records and changed the titles to 2010. I did a "compact and repair" to the 2010 database. But when I start to enter records for 2010 the ID# is 2550. How can I reset the ID# back to start at 1? On many of my queries I have the results be for example 28 Caucation, 43 African American, 65 Hispanic, 4 Asian, 2 Native American. I need these numbers separate like they are but I would like a total line at the bottom...

attachments removed #2
I receive the following message when receiving emails with attachments: "OE removed access to the following unsafe attachments in your email: (file name)" Can this barrier be overcome? Open Outlook Express and select Tools | Options | Security tab and uncheck the second box from the top concerning attachments. "Lisa Fry" <lisafry@iprimus.com.au> wrote in message news:716101c483f0$94d281f0$a501280a@phx.gbl... >I receive the following message when receiving emails > with attachments: > "OE removed access to the following unsafe attachments in > yo...

Alternatives to VBA in Excel #2
Hi, excuse my cross-posting, but I do not know which NG is the appropriate for my problem. I wrote an Excel-Macro in VBA which reads a text file, interprets this file and then does in Excel what has o be done: format cells, put values to it, storing files, inserting worksheets etc. This works well, as designed, but is not very fast. So I look for a way to increase speed. I have Visual C++ 6 in Visual Studio. Can I rewrite my macro in Visual C++ so that it gets faster? If yes, where do I find "easy to understand" descriptions how to do this? What do I need additionally? TIA Thomas Kre...

macro
I have several columns in an income sheet. One column has a sum at the bottom. I want to be able to run a simple macro that will copy the last row of formatted cells, just above the sum total, and insert it as a new row just above the sum total so another line of data can be input. But I want the sum total to sum also the newly inserted row. Presently I can't figure out how to do it. Example A B C 1 24 Oct Dollars In $100 2 25 Oct Dollars out $25 3 Total ...

Add Sheet Cells into 1 sheet
People i have a excel spread sheet with many sheets inside it one for each day dating back 3 months sheets are named like 1st july05 till 1st oct05 1 for each day and blank sheets for weekends i want to add the totals of field b25 on all sheets onto a new sheet named totals can this be done? Mr. Banner, =SUM('First Sheet:Last Sheet'!B25) -- Earl Kiosterud www.smokeylake.com "Mrbanner" <mrbanner@swiftdsl.com.au> wrote in message news:1129255291.945224.222600@f14g2000cwb.googlegroups.com... > People i have a excel spread sheet with many sheets inside it one for ...

Merging data from 2 workbooks
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel How can I merge data into 1? <br><br>Some of the data Names are the same but I need to combine amounts in the other cells Sorry, I do not understand: your question does not have enough detail. See the excel help topic "Use names in formulas" for more. You CAN use "Sheet-scoped data names" if you define them that way, but "don't" because it can make your formulas very confusing. See http://www.cpearson.com/excel/DefinedNames.aspx Hope this helps On ...

variable subform values
I have a main form that is based upon a table that is generated via a make-table-query..i.e. the values in this table that the main form points to will vary day to day. I also have a subform that shows a list of calendar dates in one column and user entry column. The way I intend this to work is to have the user enter a qty next to the calendar date of their choosing (in the subform) for the main form item displayed. The business need is this: The main form displays the product number to be shipped. The subform displays the calendar dates and the user just has to enter th...

pivot table help #2
I am extremely new to pivot tables and trying to just get ave, max, and min of a couple of columns ... I keep getting #DIV/0! errors and don't know what the problem is. any ideas anyone??? -- evg1 ------------------------------------------------------------------------ evg1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35043 View this thread: http://www.excelforum.com/showthread.php?threadid=547887 Do you have errors in any of those fields in the raw data? Do you have any numbers in the field that you're averaging? evg1 wrote: > > I am ...

How to make chart columns thicker
Hi I am using excel 2007. I am trying to create a chart with 20 weeks in the x axis. The chart type is Column, clustered column. The chart displays two lots of data. The problem is, the columns are displayed as very thin, in fact they're little more than lines, yet there is a lot of room on the chart for them to be much thicker. How do I make the columns thicker? The chart just looks daft at the moment. Thanks for any help Colin Ok, brain in gear now, I've worked out what was wrong. The problem was that the x-axes was recognising the data as a date. I told it that it was text by ...

I need a time sheet template that verifies time entered against sy
Try posting this again with your question in the message text instead of the subject. Your question got truncated. "Bob Powell" wrote: > ...

StartDoc() returns -1
Below is a section of code that I am using, it always works the first time but on the second call StartDoc always returns -1. I can't even find out the particular error. What is going wrong? //////////////////////////////////////////////// dcp.Attach(theApp.printerDC()); DOCINFO doc; // Doc info. // memset(&doc, 0, sizeof(doc)); doc.cbSize = sizeof (doc); doc.lpszDocName = "Doc"; doc.fwType=0; doc.lpszDatatype=0; doc.lpszOutput=0; int q=dcp.StartDoc(&doc); if (q<0) { return; } "Chris Baker" <ChrisBaker@discussions.microsoft.com> wrote in messag...

multiple data label formats #2
Thanks for your help Andy, I'll look into it -- ob3ron0 ----------------------------------------------------------------------- ob3ron02's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1545 View this thread: http://www.excelforum.com/showthread.php?threadid=27275 ...

Printing Long Columns
I have a simple worksheet with order numbers in column A and corresponding completion dates in column B. Since there are over 1,000 orders, it takes many pages to print the sheet, with a great deal of paper wasted on the blank area to the right of the two columns. Other than the tedious process of cutting and pasting, is there a way to arrange the information into multiple multiple columns across the page? Try this example on David's site Charlie http://www.mvps.org/dmcritchie/excel/snakecol.htm -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Cha...

Finding Cells that Total a Value
Hello Friends, I need some assistance in solving a problem. I have a spreadsheet with over five hundred lines of transactions. The sum of these transactions are creating a balance on the account. Is there any formula/macro that will help me find the transactions creating the balance? The sum of the account should be zero. To clarify, if we owe client money, there would be a transaction setting up that postive balance then a payment on the account taking it back to zero. There could be multiple transactions and then one net payment. Or we could be due to receive. So at the end of the day, th...