Sum in a column if there are 3 conditions in another column

I need to find the total from Col B if Col A is one of the 3 possibilities

    A           B
DONE	  0
DONE	60
OBS        40
POST	55
DONE	40
DONE	  0
DONE	47
POST	55

                           DONE RESULT
                           OBS RESULT
                           POST RESULT
0
Utf
5/21/2010 3:15:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

5 Replies
1185 Views

Similar Articles

[PageSpeed] 55

Try the below for total for "Post"
=SUMIF(A:A,"Post",B:B)

'Try the below for total of all three
=SUM(SUMIF(A:A,{"Post","OBS","Done"},B:B))


-- 
Jacob (MVP - Excel)


"DogmaDot" wrote:

> I need to find the total from Col B if Col A is one of the 3 possibilities
> 
>     A           B
> DONE	  0
> DONE	60
> OBS        40
> POST	55
> DONE	40
> DONE	  0
> DONE	47
> POST	55
> 
>                            DONE RESULT
>                            OBS RESULT
>                            POST RESULT
0
Utf
5/21/2010 3:24:01 PM
To sum for "done"...

=SUMIF(A1:A10,"done",B1:B10)

-- 
Biff
Microsoft Excel MVP


"DogmaDot" <DogmaDot@discussions.microsoft.com> wrote in message 
news:29093D30-1E44-4A15-A2B8-FF84CE4E87EE@microsoft.com...
>I need to find the total from Col B if Col A is one of the 3 possibilities
>
>    A           B
> DONE   0
> DONE 60
> OBS        40
> POST 55
> DONE 40
> DONE   0
> DONE 47
> POST 55
>
>                           DONE RESULT
>                           OBS RESULT
>                           POST RESULT 


0
T
5/21/2010 3:25:09 PM
Hi Jacob,

A quick question
Is it possible to replace the criteria inside the curley brackets with cell 
references   (I'm getting an error message)? 

Thanks

Paul

"Jacob Skaria" wrote:

> Try the below for total for "Post"
> =SUMIF(A:A,"Post",B:B)
> 
> 'Try the below for total of all three
> =SUM(SUMIF(A:A,{"Post","OBS","Done"},B:B))
> 
> 
> -- 
> Jacob (MVP - Excel)
> 
> 
> "DogmaDot" wrote:
> 
> > I need to find the total from Col B if Col A is one of the 3 possibilities
> > 
> >     A           B
> > DONE	  0
> > DONE	60
> > OBS        40
> > POST	55
> > DONE	40
> > DONE	  0
> > DONE	47
> > POST	55
> > 
> >                            DONE RESULT
> >                            OBS RESULT
> >                            POST RESULT
0
Utf
5/24/2010 2:58:03 PM
Hi Paul,

you could replace the criteria with a range if you used SUMPRODUCT instead 
of SUM.

=SUMPRODUCT(SUMIF(A:A,D2:D4,B:B))




"Dazed&Confused" <DazedConfused@discussions.microsoft.com> wrote in message 
news:2B08D6EE-A2BB-4FEC-A073-C4EA7D6B8835@microsoft.com...
> Hi Jacob,
>
> A quick question
> Is it possible to replace the criteria inside the curley brackets with 
> cell
> references   (I'm getting an error message)?
>
> Thanks
>
> Paul
>
> "Jacob Skaria" wrote:
>
>> Try the below for total for "Post"
>> =SUMIF(A:A,"Post",B:B)
>>
>> 'Try the below for total of all three
>> =SUM(SUMIF(A:A,{"Post","OBS","Done"},B:B))
>>
>>
>> -- 
>> Jacob (MVP - Excel)
>>
>>
>> "DogmaDot" wrote:
>>
>> > I need to find the total from Col B if Col A is one of the 3 
>> > possibilities
>> >
>> >     A           B
>> > DONE   0
>> > DONE 60
>> > OBS        40
>> > POST 55
>> > DONE 40
>> > DONE   0
>> > DONE 47
>> > POST 55
>> >
>> >                            DONE RESULT
>> >                            OBS RESULT
>> >                            POST RESULT 

0
Steve
5/24/2010 3:19:00 PM
Hi Steve,

The trouble with SUMPRODUCT is that it eats memory and slows this ancient PC 
to a crawl.

Oh well

Paul

"Steve Dunn" wrote:

> Hi Paul,
> 
> you could replace the criteria with a range if you used SUMPRODUCT instead 
> of SUM.
> 
> =SUMPRODUCT(SUMIF(A:A,D2:D4,B:B))
> 
> 
> 
> 
> "Dazed&Confused" <DazedConfused@discussions.microsoft.com> wrote in message 
> news:2B08D6EE-A2BB-4FEC-A073-C4EA7D6B8835@microsoft.com...
> > Hi Jacob,
> >
> > A quick question
> > Is it possible to replace the criteria inside the curley brackets with 
> > cell
> > references   (I'm getting an error message)?
> >
> > Thanks
> >
> > Paul
> >
> > "Jacob Skaria" wrote:
> >
> >> Try the below for total for "Post"
> >> =SUMIF(A:A,"Post",B:B)
> >>
> >> 'Try the below for total of all three
> >> =SUM(SUMIF(A:A,{"Post","OBS","Done"},B:B))
> >>
> >>
> >> -- 
> >> Jacob (MVP - Excel)
> >>
> >>
> >> "DogmaDot" wrote:
> >>
> >> > I need to find the total from Col B if Col A is one of the 3 
> >> > possibilities
> >> >
> >> >     A           B
> >> > DONE   0
> >> > DONE 60
> >> > OBS        40
> >> > POST 55
> >> > DONE 40
> >> > DONE   0
> >> > DONE 47
> >> > POST 55
> >> >
> >> >                            DONE RESULT
> >> >                            OBS RESULT
> >> >                            POST RESULT 
> 
0
Utf
5/24/2010 3:35:02 PM
Reply:

Similar Artilces:

Integration Manager and Analytical Accounting #3
Hi, Has anyone used integration manager to update General Ledger transactions that include Analytical Analysis information? We have volumes of transactions in excel with analysis (grants, projects & locations) that we need to integrate to GL. Any suggestions? Patrick There is no off-the-shelf adapter for Analytical Accounting in Integration Manager. Microsoft's solution is to use eConnect, which adds a lot of complexity. You may not want to buy more software to solve a problem caused by what you already own. However, you might consider a product from eOne Solutions (develo...

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...

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 ...

military time #3
can I format cells in Excel to express time in military terms e. g. 14:45 hi, yes. Format>cells>time>pick from list Regards Frank >-----Original Message----- >can I format cells in Excel to express time in military terms e. g. 14:45 >. > ?? Not sure what you mean because my excel defaults to that anyway if you type 14:45 in ?? if your dosnt right click and format cells click on time and select from there -- scottymelloty ------------------------------------------------------------------------ scottymelloty's Profile: http://www.excelforum.com/member.php?acti...

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 ...

Pivot Table Problem #3
I have a pivot form in Excel 2000 that I would like to look like this monday tuesday wednesday Tota Sales 20.00 23.00 34.00 77.0 Tax 3.00 6.00 9.00 18.0 instead it looks like thi monday tuesday wednesday Tota Sales 1 1 1 Tax 1 1 1 I want the data field to display the info as it was entered by the user not give me a sum of the field, or a count of the field.. The data source is from a...

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...

=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...

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...

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...

Referencing Subform from another form 01-04-10
I have a search form and am trying to pick up data from another form to be displayed in my search form when criteria matches. The search form worked perfectly until I changed the way my database worked and changed some forms. Now I am getting #Name in my text fields of the search form. Some background information regarding my database is that the search form is currently bound to a table risk. I have a data entry form (ptinfo) that has tabs (ptinfo,incident,complaint). The form ptinfo also is bound to table risk. On the incident tab i have a subform occurrence where data is enter...

office 2007--word opens documents using the 1/3 ofcenter space
after overnight updates, office word opens in center 1/3 of avail space. Content on page is too small to read. Office 2007 enterprize, Win 7 ultimate, Acer aspire 6920, 3 gigs ram all 32 bit -- Art Have you tried dragging the Zoom back to 100%? -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Artsr3" <Artsr3@discussions.microsoft.com> wrote in message news:4905DC57-473A-4B50-9FEE-8343D4385A9D@microsoft.com... > after overnight updates, office word opens in center 1/3 of avail space. > Conten...

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 ...

Simple 3D Column
I am currently getting my blood tested every two weeks and have made a simple 3d column chart with the data. This shows the results well. However i would like a red line showing the maximum and minimum levels that would show the "Normal" parameters of the blood. For instance the column for this week rises to 11.5 and the top and bottom (Normal) parameters are 13.5 to 18 so I would like a line at 13.5 and one at 18 with my column of 11.5 over the lines. Hope this is clear as this is one of my first attempts at charting. -- thepict@albaweb --------------------------------------...

Is there a way to rotate a column of data into a row of data?
Hi all, Is there a way to rotate a column of data into a row of data, in Excell 2003? Let's say B8 - B1000, there are so many of them... I want to rotate it into a row form... Thanks a lot! You'll need to explain the rules a little better.... You want to convert 992 vertical cells into one row .....but Excel 2003 only has 256 columns. *********** Regards, Ron XL2002, WinXP "cfman" wrote: > Hi all, > > Is there a way to rotate a column of data into a row of data, in Excell > 2003? > > Let's say B8 - B1000, there are so many of them... I...

Default column layout in Smartlist Builder
When generating new Smart list using the Smartlist Builder tool it would be great to allow for a means of identifying what the default column layout to be rather that having to create a favorite to do that. For Example. If SLB wanted to place columns 1, 2, 3, 4, 5 as such you could tell it to make it 5, 4, 3, 2, 1 instead in the root folder of the new SL created ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see th...

Added data in columns
I am new to Excel. I have a project I am working on in which I have a column with a value from 1 to 5. Each number (and in some columns letters) represents a different answer. Is there a way to add the total number of each value? For instance I have over 2000 rows with data. In column "N", an answer can be either 1,2,3,4 or 5. How would I figure out how many answers are "1", how many are "2" an so on. Likewise there is a column with a yes/no answer. How can I see the total number of Yes's and how many No's there are? I appreciate any help. Hi ...

Sum Question
I am using Excel 2000 I have the following very simple formula in column j Sheet1 =SUM(B5*H5) I have this copied all the way down to line 40. It shows 0 in all of the cells all the way down. I would like for there to be a way that the cell would not show anything in it UNLESS there was something that it was calculating. For example. I have entered data in column b and column h through line 10, but it still shows 0 in column J all the way down to line 40. I realize that it is showing these 0's b/c I have placed the formula there, but is there a way that it will still calculate but only ...

Line
I have a table with 4 columns and I want to show three columns as bars on one axis and one as a line on the other axis but the standard format always splits the table into two bars and two lines. Even if I transfer the third column into the primary axis it still stays as a line. Any help will be great. Ta, Michael Hi Michael, Best thing to do is create a Column chart based on all 4 data series. Them select the data series that you want to plot as a line. Right click and pick Chart Type from the popup menu. Pick the appropriate line chart. Michael C wrote: > I have a table with 4 col...

Problems with CRM 3 Outlook client sync.
Hi, I have joined two computers to a Windows Server 2003 domain. After that i've installed CRM 3 Outlook Client on two laptops with Vista. The installation went smoothly. I have desktops on the network, where clients are working properly. On these two laptops when I start Outlook with CRM clients get this message: You can not start synchronization process on this computer. This process can only run on a single client computer user. Customer synchronization should be the computer, which usually is online (for example, desktop computer), or basic computer user. To change the client s...

Multiplie a column by 2
Hey, Got a column I wanne multiplie by 2. It goes from I6 to I2237 so it takes some time if I gotta do it manual. Each cell gotta be multiplie by 2. Hope this made any sense. Thanks -- Message posted from http://www.ExcelForum.com Hi - put the '2' in an empty cell and ocpy this cell - select I6:I2237 - goto 'Edit - Paste Special' and choose 'Multiply' -- Regards Frank Kabel Frankfurt, Germany > Hey, > > Got a column I wanne multiplie by 2. > It goes from I6 to I2237 so it takes some time if > I gotta do it manual. Each cell gotta be multiplie ...