summing two columns of different data

I need to sum a dollar total from two different types of columns. One column 
identifies the item and another column associates a dollar amount with that 
item. The items are different tree species and the dollar amounts are the 
pruning costs associated with a specific tree species in a given year.  For 
example:

Column A		Column B		Column C
Species		2011		2012
Lemon tree			$100
Apple tree		$50
Lemon tree			$150
Cherry tree	$50	                $50

I want to sum the total dollar amount to prune only the lemon trees in 2012. 
Presently, I am using a COUNTIF to sum the total number of lemon trees, but I 
need to also sum the cost of pruning just those lemon trees.

Column D		Column E		Column F
Trees by species	Total		2012 pruning cost
Lemon trees	2		$250 (need formula to get this sum)

Using one formula, can I fill the cells in both Column E and Column F?

-- 
Dick Scoppettone
0
Utf
5/24/2010 6:11:01 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
540 Views

Similar Articles

[PageSpeed] 45

=SUMIF($A:$A,"Lemon tree",B:B)
Will give you total for 2011. Copy one cell to the right to get total for 
2012.

-- 
Best Regards,

Luke M
"Dick Scoppettone" <dick@naturefirst.net(donotspam)> wrote in message 
news:7D823300-54A5-4838-945B-126B61239E66@microsoft.com...
>I need to sum a dollar total from two different types of columns. One 
>column
> identifies the item and another column associates a dollar amount with 
> that
> item. The items are different tree species and the dollar amounts are the
> pruning costs associated with a specific tree species in a given year. 
> For
> example:
>
> Column A Column B Column C
> Species 2011 2012
> Lemon tree $100
> Apple tree $50
> Lemon tree $150
> Cherry tree $50                 $50
>
> I want to sum the total dollar amount to prune only the lemon trees in 
> 2012.
> Presently, I am using a COUNTIF to sum the total number of lemon trees, 
> but I
> need to also sum the cost of pruning just those lemon trees.
>
> Column D Column E Column F
> Trees by species Total 2012 pruning cost
> Lemon trees 2 $250 (need formula to get this sum)
>
> Using one formula, can I fill the cells in both Column E and Column F?
>
> -- 
> Dick Scoppettone 


0
Luke
5/24/2010 6:37:34 PM
Take a look SUMIF() in help menu


"Dick Scoppettone" wrote:

> I need to sum a dollar total from two different types of columns. One column 
> identifies the item and another column associates a dollar amount with that 
> item. The items are different tree species and the dollar amounts are the 
> pruning costs associated with a specific tree species in a given year.  For 
> example:
> 
> Column A		Column B		Column C
> Species		2011		2012
> Lemon tree			$100
> Apple tree		$50
> Lemon tree			$150
> Cherry tree	$50	                $50
> 
> I want to sum the total dollar amount to prune only the lemon trees in 2012. 
> Presently, I am using a COUNTIF to sum the total number of lemon trees, but I 
> need to also sum the cost of pruning just those lemon trees.
> 
> Column D		Column E		Column F
> Trees by species	Total		2012 pruning cost
> Lemon trees	2		$250 (need formula to get this sum)
> 
> Using one formula, can I fill the cells in both Column E and Column F?
> 
> -- 
> Dick Scoppettone
0
Utf
5/24/2010 6:45:01 PM
Thanks, Luke - works like a charm!!!
-- 
Dick Scoppettone


"Luke M" wrote:

> =SUMIF($A:$A,"Lemon tree",B:B)
> Will give you total for 2011. Copy one cell to the right to get total for 
> 2012.
> 
> -- 
> Best Regards,
> 
> Luke M
> "Dick Scoppettone" <dick@naturefirst.net(donotspam)> wrote in message 
> news:7D823300-54A5-4838-945B-126B61239E66@microsoft.com...
> >I need to sum a dollar total from two different types of columns. One 
> >column
> > identifies the item and another column associates a dollar amount with 
> > that
> > item. The items are different tree species and the dollar amounts are the
> > pruning costs associated with a specific tree species in a given year. 
> > For
> > example:
> >
> > Column A Column B Column C
> > Species 2011 2012
> > Lemon tree $100
> > Apple tree $50
> > Lemon tree $150
> > Cherry tree $50                 $50
> >
> > I want to sum the total dollar amount to prune only the lemon trees in 
> > 2012.
> > Presently, I am using a COUNTIF to sum the total number of lemon trees, 
> > but I
> > need to also sum the cost of pruning just those lemon trees.
> >
> > Column D Column E Column F
> > Trees by species Total 2012 pruning cost
> > Lemon trees 2 $250 (need formula to get this sum)
> >
> > Using one formula, can I fill the cells in both Column E and Column F?
> >
> > -- 
> > Dick Scoppettone 
> 
> 
> .
> 
0
Utf
5/25/2010 7:00:04 PM
Thanks - your SUMIF started me in the right direction, then Luke closed the 
deal.
-- 
Dick Scoppettone


"Teethless mama" wrote:

> Take a look SUMIF() in help menu
> 
> 
> "Dick Scoppettone" wrote:
> 
> > I need to sum a dollar total from two different types of columns. One column 
> > identifies the item and another column associates a dollar amount with that 
> > item. The items are different tree species and the dollar amounts are the 
> > pruning costs associated with a specific tree species in a given year.  For 
> > example:
> > 
> > Column A		Column B		Column C
> > Species		2011		2012
> > Lemon tree			$100
> > Apple tree		$50
> > Lemon tree			$150
> > Cherry tree	$50	                $50
> > 
> > I want to sum the total dollar amount to prune only the lemon trees in 2012. 
> > Presently, I am using a COUNTIF to sum the total number of lemon trees, but I 
> > need to also sum the cost of pruning just those lemon trees.
> > 
> > Column D		Column E		Column F
> > Trees by species	Total		2012 pruning cost
> > Lemon trees	2		$250 (need formula to get this sum)
> > 
> > Using one formula, can I fill the cells in both Column E and Column F?
> > 
> > -- 
> > Dick Scoppettone
0
Utf
5/25/2010 7:02:02 PM
Reply:

Similar Artilces:

Data validation issue with multiple columns
I need something that forces a 2nd response if the word Other is chosen from a drop down. A B C Name Reason Note ----------------------------------------- Jim complete Bob Other left early If the response is anything but other then no note is required, but I want to require something added to the notes if other is selected. A and B are validation drop downs and C is a free form notes column. Thanks HI You will need some VBA code to achieve this Private Sub Worksheet_Change(ByVal Target As Range) Dim tr As Long,...

Setting up a virtual SMTP Server at a different Site
I have an exchange server in site1 and would like to setup a virtual smtp server in IIS 6, at site2 to send customer emails instead of travelling over a T1 to site1 in order to get sent. I am not entirely sure how to set this up. Also not sure how to setup and configure dns, reverse dns lookups in the virtual smtp server. Any help or useful documentation on how to accomplish this would be great. -Mike How will you send/submit email to IIS? If it's through Outlook client, it will be submitted to your Exchange server which will send the outbound mail. If you intend to use some progra...

Data Form Problem
I have a fairly simple table, with about 7 columns of data which I have created for a co-worker. Most rows contain a single line of data, but the last one of the fields uses "wrap text" and may have as many as 7-10 lines in that field. I am trying to make it so she can use the Data Form method for data input. I am able to get the Form to be created, but it says I only have one record . . . when very clearly there are many more records. Does anyone know why this might be? I thought it might be because of the "wrap text" in the one column . . . and so I tried c...

How to Perform Lookup on Alphanumeric Data?
I've found a few hundred posts about VLOOKUP and HLOOKUP functions and their limitations. But I've not found a post that addresses my particular situation. Is there a way in Excel to get a VLOOKUP to find the next highest value in a Table where the lookup data is alphanumeric? I have a list of names in Column A and I'm trying to perform a lookup on a table in another spreadsheet using: =VLOOKUP(A20,'Employee List'!$A$2:$G$1769,2) But the problem is that the lists are from different sources, so the spacing, punctuation, and inclusion of middle names/initials varies. The...

Advanced Find error when adding revenue column
We are using CRM 4.0 and would like to find all contacts who have parent accounts with a lifetime revenue over $50,000. The query runs great until we add the Lifetime Revenue column to display in the results. This is where we get the error. This is a hard error and can be created by using the case entity as well and linking to the customer. As soon as you add the Lifetime Revenue to the results view you get the error also. It is throwing an application error on the CRM server. Does anybody else get this error? Thanks, -- Don Meyer Double-Take Software On 19.08.2009 15:15, Don Meyer ...

Create chart using part of data table (data legend)
Hi I want to create a chart use only a part of data table (data legend) but I want the data table (data legend) to show all data. Because data is too much I just want the chart to show only two of them but I want to see the other data values on the data table. Is that possible ?? ...

sum #4
Hi, Is there a way to find what numbers equal a sum in two seperate columns? I am reconciling debits and credits and there are multiple entries in debits that may equal an entry in credits or visa versa. example: debits credits 54 14 45 99 198 24 where 54 plus 45 equals 99. My report is more complex with approx. 250 entries. Any help will be appriciated. Thanks so much. Robert --- Message posted from http://www.ExcelForum.com/ Hi if you have that many numbers (>250) I doubt this is feasible either in Excel or any other application. Too many...

How can I swap two xml nodes that belong to the same parent?
How can I swap two xml nodes that belong to the same parent? Examples would be great! Thank you, Sasha I suppose you could use the InsertBefore or InsertAfter methods. Just locate nodes adjacent to those you want to swap. "Sasha" <no@no.com> wrote in message news:#dlRBygrDHA.2820@TK2MSFTNGP10.phx.gbl... > How can I swap two xml nodes that belong to the same parent? > > Examples would be great! > > > Thank you, > Sasha > > ...

Error when getting External data in Excel
I am trying to populate multiple cells in an excel sheet with the results of a query executed on a sybase database. I get the query parameters, which I get from some pre-defined cells in the Excel sheet. Obviously, the parameter values vary and accordingly the query executed gets different results. This works perfectly or some cells, but for other cells I get this error: [INTERSOLV][ODBC SQL Server driver] Timestamp parameters with zero scale must have a precision of 13, 16, or 19. Parameter number: 3, precision:34. Parameter #3 is a date and I am passing values like '4/1/2005' ...

Find column number
I have a table with columns headed 1 to 13 in cells A1 to m1. The rest of the table displays the answer 1 once per row with the rest of the columns in each row empty. I need to display which column the 1 appears in for each row, i'm sure there is a formula for this and any help would be much appreciated as I have over 3000 rows to get a column number for. Thanks James Put this in cell N2 (for example): =3DMATCH(1,A2:M2,0) Then you can copy it down as far as required. Hope this helps. Pete On Jul 28, 11:42=A0am, "j.farr3ll" <j.farr...@googlemail.com> wrote: > I h...

Extracting Data from CRM
Hi All, We have just acquired a company which we are in the process of integrating into our IT infrastructure. Both organisations currently have CRM running i need to do the following: 1. Extract SALES data from one system and import it into the other. 2. Extract SERVICE data from one system and import it into our 3rd party helpdesk software. Has anyone had any experience of this or have any suggestions as to how it might be done? Thanks in advance, Chris use data migration for crm "cjclifford@ntlworld.com" wrote: > Hi All, > > We have just acquired a company whi...

Setting up different address in the reply-to field
Hi, I am using Outlook 2003 and in order to keep my privacy I would like to reply to a message in such way that the recipient would see different to my original address in the reply-to field. How do I do this? Is it possible at all for the recipient to find out where the letter originated from? I am using a redirection email alias which doesn't store mail just redirects plus it offers spam and virus filtering. Thank you. Depends if the SMTP path gives the info. Notice the header below from a piece of mail I sent to a mailing list. The message was initially received from this ...

Two entities can have only one distinct relationship between them.
From: BLaw - view profile Date: Fri, Aug 25 2006 11:35 am Email: "BLaw" <brad...@gmail.com> Groups: microsoft.public.crm.deployment Not yet ratedRating: show options Reply | Reply to Author | Forward | Print | Individual Message | Show original | Remove | Report Abuse | Find messages by this author I have read the following: "Two entities can have only one distinct relationship between them. Therefore, you could not create relationships in a way thjaat a form has two or more lookups that connect to the same custom entity." The second paragraph sates: &quo...

dll not able to insert data
i dont understand what went wrong with this code....i am not able to insert data....with my dll can anybody help me out there....pls I have include in project->setting->link->object/library module->c:/ .lib //This is dll function short setQuoteToDB(BSTR* strDateTimeStamp, short* intMarketNo, BSTR* strMarketName, BSTR* strBid, BSTR* strAsk, short...

2007 Table Linked Charts
I have a dataset with monthly data over several years. I want to chart only the current calendar year's monthly data, and have the chart update automatically as each of the newest month's data is added. One solution is to hide the prior years' monthly data rows in the table which removes them from the chart. This limits other charts that might be linked to the same table where they chart the whole or an earlier date range of the dataset. Linking tables doesn't work since the mirroring tables don't auto update as a new month is entered. Has anyone developed a better sol...

Search in multiple columns using VLOOKUP?
Hi, I'm looking for a formula to find a row in a spreadsheet with two matching fields which returns the value of a field in a certain column in that row. VLOOKUP only supports searching in one column as far as I know. Let me give you an example to clarify my request. Please read the following text in a non-proportional font like Courier New. Worksheet A A B C D 0 4 7 0 3 6 1 0 7 5 0 6 Remarks: either column A or B is always zero, and the other one non-zero. I need the formula to use the value of the field which is non-zero. Further on, the value of the column C has to match...

Chart columns
Hi, I am creating a series of charts. I remember someplace to change to shape of the column to round the corners. I can't seem to find at anymore, any help? I am using excel 2000. Thank you for any help you can give me. On Wed, 29 Oct 2003 07:59:37 -0800, rmacneil@comcast.net = <anonymous@discussions.microsoft.com> wrote: > Hi, > I am creating a series of charts. I remember someplace to > change to shape of the column to round the corners. I > can't seem to find at anymore, any help? I am using excel > 2000. Thank you for any help you can give me. I ...

what is 'too many different cell formats' reply?
Tommy Read this KB article On Thu, 16 Dec 2004 19:29:01 -0800, "tommy" <tommy@discussions.microsoft.com> wrote: Check these out: http://support.microsoft.com/default.aspx?scid=kb;en-us;213904 http://www.vbapro.com/xl_formats.htm tj "tommy" wrote: > I think Gord meant to include this article link: XL: Error Message: Too Many Different Cell Formats http://support.microsoft.com/?id=213904 Gord Dibben wrote: > Tommy > > Read this KB article > > > > On Thu, 16 Dec 2004 19:29:01 -0800, "tommy" <tommy@discussions...

data migration/ map
I have entered discount lists and want to utilize those discounts for generating quotes. Please advie as to how to go about doing the same. In some previous posts, it was indicated to go around using the Price Lists rather than the discount lists. I have entered the lists there as well, but am unsure as to how to use them for Quote generation and other applications. Please advice.!! thanx ...

Unhiding Protected Rows and Columns? is it possible?
I have a workbook with a number of worksheets that all interlink. I have protected a number of cells in one of the sheets. the problem arrises in that the unprotected cells cannot be hidden or indeed unhidden when the sheet is protected. Is there a way I can protect cells whilst still keeping the formatting ability to widen / hide rows etc. thanks -- guilf ------------------------------------------------------------------------ guilf's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25608 View this thread: http://www.excelforum.com/showthread.php?threadid=39...

Gridlines on Column Chart
Hello- I am trying to create a stacked column chart with gridlines that only appear for every 2 columns. How can I do this in Excel 2007? All I can find is how to insert a major gridline between every column. Thanks, Sean Hi, Format the X axis, on the Axis Options section set 'Interval between tickmarks' to 2. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "SeanF74" <SeanF74@discussions.microsoft.com> wrote in message news:E1BE2939-AB52-4FB1-8A0F-ACA9EFDB4CAD@microsoft.com... > Hello- > > I am trying to create a stacked ...

Control for displaying Data
Hi, I want to create an active control that will be having a dialog. On this dialog I want to show a table consisting of set of records. The data to this table will be coming from the web and will be refreshing again and again. Could you suggest, which control should I use to have the things done. I did not find Datagrid control suitable at this place. What are the other options available. FYI: Iam using VC++ 6.0 THANKS IN ADVANCE, Vijay You can use a list control (listview). But the popular one with a lot of features is: http://www.codeproject.com/miscctrl/gridctrl.asp --------------...

sum of timesheet
Let's say A1: 9 am A2: 12 pm A3: 12:30 pm A4: 3 pm I need the total in A5 to calculate how many hours worked, but also if I write "off" in A1:A4, I need A5 to say "off", so basically I need either the total hours worked or the word "off" in A5. Is this possible? Thanks for any help, Try =IF(COUNTIF(A1:A4,"off")=4,"off",SUM(A1:A4)) -- HTH Bob "nidabland" <nidabland@discussions.microsoft.com> wrote in message news:B75ABF76-87B7-4F0C-B249-44885D6F4305@micro...

Charts & Hidden Data
Hi, If I hide the row/columns containing the source data of a chart the respective series disappears from the chart. Is there any way to hide chart source data without affecting the chart? Many thanks, Jason In XL2003 (and before) with chart selected: Tools | Options |Chart and uncheck the box "Plot visible cells only" In XL 2007: Follow this path: click the chart; use Chart Tools|Design|Selected Data; look for button labelled "Hidden and Empty Cells"; complete dialog box as needed. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from ema...

lookup date, sum up total...
Hello all, Here is my situation: Worksheet 1 A B C D 1.)1/1/05 Data Data P&L 2.)1/2/05 Data Data P&L .. .. .. 365.) 12/31/05 Data Data P&L ............................................................ Worksheet 2 A B 1.) January =IF(Worksheet1=January, then sum all january P&L (Ignore other months)) 2.) February =IF(Worksheet1=February, then sum all February P&L (Ignore other months)) .. .. .. 12.) December =IF(Worksheet1=December, then sum all DecemberP&L (Ignore other months)) what formula do I need to...