Sum column using 2 other columns as criteria

My table has 3 columns: A, B, and C.  C has all numeric values.

I want to SUM values in C for all rows where value in A equals "a" and value 
in B equals "b".

Can anyone tell me a formula construct for this?

Thanks! 


0
hall (126)
4/14/2007 3:32:34 AM
excel 39880 articles. 2 followers. Follow

1 Replies
443 Views

Similar Articles

[PageSpeed] 28

=SUMPRODUCT((A1:A14="a")*(B1:B14="b")*(C1:C14))

Adjust the range as required

Regards

Trevor


"Hall" <hall@garp.org> wrote in message 
news:OAg8KWkfHHA.588@TK2MSFTNGP06.phx.gbl...
> My table has 3 columns: A, B, and C.  C has all numeric values.
>
> I want to SUM values in C for all rows where value in A equals "a" and 
> value in B equals "b".
>
> Can anyone tell me a formula construct for this?
>
> Thanks!
> 


0
Trevor9259 (673)
4/14/2007 3:49:24 AM
Reply:

Similar Artilces:

How to perform sum sum sum...
How to perform sumation within that particular item but the item is not unique...means item 1 has its own quantity and same goes to item 2...but in the same table... -- Message posted via http://www.accessmonster.com On Tue, 17 Apr 2007 05:28:12 GMT, "EMILYTAN via AccessMonster.com" <u33296@uwe> wrote: >How to perform sumation within that particular item but the item is not >unique...means item 1 has its own quantity and same goes to item 2...but in >the same table... Group By the item. For a more detailed answer, please post a more detailed question (with a des...

Summing up user defined results
Hi all - I'm new to VBA programming in Excel and so any help i'd totally love! I'm currently writing a function. Objective: user can select rows (do not have to be sequential). User clicks on button. UserForm appears with summed results from ONLY rows that he selected. What I have now, well it doesn't work: Sub Button6_Click() Dim i As Integer Dim totalNumbers As Integer Dim aRange As range For Each a In Selection.Areas 'MsgBox "Area " & i & " of the selection contains " & _ ' a.Rows.Count & " rows." &...

Multiple variables to sort and sum, return values<0 with sum refer
Can anyone help me with this one please? I have this table of data. I'd like to be able to write a formula(s) which sums the No according to date and code, but then only returns a sum value (with the code and date in the two adjacent cells) if there is a value greater than 0. Code Ref No Date 1 G/032/05/999 400600 212 19/03/2010 2 G/032/03/001/999 400500 50 19/03/2010 3 G/032/02/001/001 400400 170 19/03/2010 4 G/032/05/999 400600 315 19/03/2010 5 G/032/03/001/999 400500 300 19/03/2010 6 G/032/05/999 400600 202 19/03/2010 7 G/032/03/001/002 40...

sum subform to form
=Sum([Forms]![frmCamPledgeList].[frmCamPledgeListSub].[Amount Pledged]) Form (frmCamPledgeList) with subform frmCamPledgeListSub). Subform has a field named Pledge Amount. I want to put a field on the form that gives me a total of the subform's Amount Pledged. I have tried the above, but I get an #error in the unbound text box. Do you need any more info? Any thoughts on why this does not work? Thanks in advance, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200912/1 The proper syntax is =Sum([Forms]![frmCa...

Excel File in Use Notification Missing??
We are using Windows Server 2000 & several versions of Excel (2000 / XP / 2003). When opening Excel files from the network, SOME users get the Notification that the File is In Use - locked for editing by <user> - with a choice to Open as Read-Only, Notify when avail, or Cancel. The pblm is that NOT EVERYBODY gets this msg - some simply have the file open up with the word (Sharing) in the Title Bar!! When you've got a deskful of work to input you're not always looking at the Title Bar to see if the file is Shared or not. I first thought this was caused by diff vers o...

How to Combine Data from Several Columns?
Hi Everyone I have attached a file which contains what I need to resolve Basically, I need to combine the data in several columns together int one column. Is there a way I can do it with formulas or vb code? would really appreciate if any experts out there can help me. Thanks and Regards Kelvi Attachment filename: book1.xls Download attachment: http://www.excelforum.com/attachment.php?postid=46105 -- Message posted from http://www.ExcelForum.com Hi Kelvin! Probably the answer will be something like: =A1&" "&B1&" "&C1 ...

variable allocation accounts #2
i want to setup and use variable allocation accounts for my company and on the help menu it says to go to cards>>financial>>variable allocation.... but i don't have that option in cards>>financial... is it something seperate i have to buy or a security setting or...???? thanks for you help! Josh, it's standard functionality in GP. Probably a security setting needs be changed so you can see it on the menu. Frank Hamelly, MCP-GP NOVA Solutions LLC Melbourne, FL ...

Calculate sum of a field within the query
I have a query that returns the details for a failure record during a specified time period. Where if I search between 1/1/08 and 1/31/08 the query returns the records of all failure records during that period. The row shows the part number and the total parts failed for that record. I need the query to then sum the total part failures for the previous 12 months. So I would see the following: Record # | part # | parts failed | Origination Date| Total Parts failed past 12 months 44444 | x | 4 | 1/2/08 | 200 44445 | Y |1 ...

using intersection of ranges
Need to specify the intersection of two named ranges, but require either one or both of them to be referenced to other cells. In other words, someone would enter the range which is one or both of the two, and I need the formula to look at that cell and use its name in the intersection argument. Any ideas? I have tried TEXT, but that doesn't seem to do it. Suppose the rangenames are rangeA and rangeB, and the names are in D2 and D3 =INDIRECT(D2) INDIRECT(D3) Note the spaces between the two functions; space is the intersection operator. Now you'll have to test for one or two...

Cells stay highlighted, cannot use mouse
When I click on a cell in excel, that cell shows as clicked on, but when I move the mouse all surrounding cells become highlighted as well. So I cannot input anything anywhere, as moving the mouse around just keeps highlighting cells. And I cannot shut excel down as trying to move the mouse to a menu bar keeps highlighting cells in the worksheet. The only way to get out of excel is to End Task. I have come across this problem before, but I don't know how I inadvertently activate it, and to stop it was some combined of Ctrl-Enter but I can't remember what. Hopefully someone will un...

how to make this work if sum=5+n2 then sum becomes the value of s.
I want to get or create a formula in excel or access that allows me to keep a running total of my supplies ie... I have 2 pens, remove one and receive 2. answer in the cell becomes 3 pens then if I zero out the received cell the inventory cell still remembers that I have 3 pens not 4 pens (because I received 2 more pens) and visa versa when I zero out the received cell it remembers I have 3 pens not 1 pen(because I removed 1 pen) I believe the method you suggest is flawed. You have no audit trail. If the number on the sheet doesn't match your actual inventory, how will you figure out...

Can DoCmd.TransferDatabase be used to move table to BE
Hi O' Wonderous gurus, Can DoCmd.TransferDatabase move a table from the Front End to the Back End? I've tried the following syntax without success. A msgbox shows the strBackendPath to be correct. DoCmd.TransferDatabase acExport, "Microsoft Access", strBackendPath, acTable, "tblNewTemplate", strNewTbl I'm experimenting with a few things & if I make a small form that I can quickly import into any database I'm developing which has the ability to transfer tables from the front end to the back end & also delete tables then it will b...

Multiple criteria VLOOKUP
Hi, I have a seven column dataset (A1:G2000) and I want to lookup a value based on 3 criteria: one for each of the 1st three columns, so where the value in column A = X, column B = Y, column C = Z, with XYZ being three criteria defined by three other cells. Can anyone point me in the right direction. I'm wanting to figure out how to do it without creating a helper column where columns A,B and C are concatenated, and using the concatenating field as a lookup key. Any help greatly appreciated, Thanks....Jason Array formula =3DINDEX(D1:D10,MATCH("x"&"y"&...

=SUM Ranges Do Not Update
I have a Excel 2000 spread sheet, with the following macro to insert new row. Sub InsertRow() ' ' Macro1 Macro ' Macro recorded 4/27/2004 ' 'GoTo label, MyString ActiveSheet.Unprotect Application.Goto Reference:="MyCell" ActiveCell.Select ActiveCell.EntireRow.Insert ActiveCell.Offset(-1, 0).Select ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).Select ActiveCell.PasteSpecial xlPasteAll Application.CutCopyMode = False ActiveCell.Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True Scenarios:=True End Sub The problem is that in the "Mycell&qu...

Using Macros to remove a name from a list
I own a small business with roughly 45 employees (mostly teenagers). There's lots of turnover. I've got a spreadsheet that I use to track tips according to the role and shift that our staff works. My manager has the ability to select the staff member who earned the tip from an in-cell drop down list (using DV). Here's my question. I want to make it really easy for my managers to add or delete staff members from the drop down list. I've created a separate worksheet with two sections: "add staff member" and "delete staff member". When they want to...

Using XmlRootAttribute for deserialization
I've seen this come up before, but in my case, things are a little more complex, and I'm having a tough time figuring out how to set an element name that works. I have a configuration file that is my serialized object (I'm using a customer deserializer to add some more XAML-like capabilities): <AppConfigObject> <RuntimeType:Kernel xmlns:RuntimeType="MyNamespace.DefaultKernel, Kernel"/> </AppConfigObject> public class AppConfigObject { [XmlElement] public Kernel {...} } Problem I have is deserializing the child node <Kernel> because it al...

Hyperlink to first blank cell in column?
(XL2007) Thanks to help from MVP Biff, I can return the row number of the first blank cell in a single-column named range using an array formula (http://tinyurl.com/qb689k). This is a dynamic range that will adjust as new items are added. I'm setting up a workbook in which I have a "Blank Master" sheet and a "Jobs List" sheet. The "Master" sheet will be copied and renamed for each month. I'd like to have a hyperlink on the "Master" sheet that will carry over to each new copied sheet that would take the user to the first blank cell in the JOB...

CRM 1.2 exam for customers
Is certification available for customers or is this only available for partners. I've read many of these postings in regards to the exam and many people mentioned getting the study material straight from Microsft rather than take the courses...how would a customer go about getting the study material? Thanks Yes, starting July 1, 2004, customers will be able to take a Microsoft Business Solutions exam at Pearson VUE or Prometric testing centers. "Jen Selmeyer" <anonymous@discussions.microsoft.com> wrote in message news:cb2901c4390d$f3ef7090$a501280a@phx.gbl... >...

Auto Sort #2
I was wandering if it is possible to sort a group automatically so tha every time the data changes it automatically sorts by a certai column? I want to auto sort a table (A2:H12) by column H is this possible ?? Many Thanks Ben -- Message posted from http://www.ExcelForum.com Benn, On the sheet that you want this to happen.... Right click on the sheet tab and select "View Code" Then copy and paste the following code: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("H2:H12")) Is Nothing Then Range("A2").Select ...

Sum value between dates problem driving me mad!
Good Evening All, I am really struggling here, have tried, nested IF's including AND's but am a bit stumped. I am fairly familiar with Arrays, but cannot seem to combine all. I have a data table (as detailed below). (Hopefully, this should be 'pastable' into Excel - it works for me) I simply wish to calculate the expected totals per month. Any help & assistance with this would be most welcome. Cheers, Mathew Note, earliest Start is 01/04/03 Start Finish Day Rate Apr-2003 May-2003 Jun-2003 Jul-2003 Aug-2003 01/04/2003 23.00 01/04/2003 09/07/2003 23.00 ...

Conditional Sum Wizard
Currently I am setting up a sheet and I am using the conditional sum wizard for formulas. Once a formula is created the sytem won't let me copy or change a formula. If I try to copy (Copy, paste special) a formula to another cell, the formula won't work anymore. If I create a formula with the wizard and afterwards change one of the parameters, the formula does not work anymore. Can anyone give me a hint on how I can solve these issues? Thanks. Please don't multi-post - you have an answer elsewhere, relating to use of CSE. Pete On Jan 6, 9:23=A0am, MarcoKoenders <MarcoKo...

stop all the columns coming up the same length in a chart
when i try to make a chart in excel all the columns come up the same length and it won't display the years either Hi, Your post is a bit lite on detail so I have more questions than answers. Is it possible you have selected the 100% stacked column chart? What data do you have and how is it laid out? Where should it be displaying the years, axis or as data labels? Cheers Andy prinshin wrote: > when i try to make a chart in excel all the columns come up the same length > and it won't display the years either -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Transfer inventory in #2
Hello, Its possible to import data from a mobile scanner in a Physical Inventory count. But is it also possible to do that in an Transfer inventory in or Purchase order, without a outgoing transfer or purchase order? One of my clients asked for this, because he receives many products without an order. And don't wan't to fill it in per hand. Hope somebody can help me. Kind regards. Raymond Bakker The Netherlands. Again, it went wrong again.. This post can be closed -- Kind regards, Raymond Bakker ReBuS b.v. The Netherlands "Raymond Bakker" wrote: > Hello, > ...

Problems removing reoccuring appointments in calendar #2
I have one user that cannot delete reoccuring appointments. He tries to delete one instance and it will not remove. He is running Outlook 2003 on Exchange Win 2000 server. Any ideas???? ...

Adding News to Outlook #2
I want to add News to Outlook. The help says to click go, but under go there is no NEWS listed. I also don't have Outlook Express, just news. Thanks in advance. Beach Runner <Bob4Health@hotmail.com> wrote: > I want to add News to Outlook. Outlook doesn't do news on its own. By default, it uses Outlook Express as its newsreader, but there are commercial add-ins you can buy that will add NNTP client capability to Outlook. > The help says to click go, but under > go there is no NEWS listed. http://www.howto-outlook.com/faq/news.htm > I also don't have Ou...