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
1429 Views

Similar Articles

[PageSpeed] 49

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:

CRM 3.0 Implementation
I am interested in the experiences of others with implementing Microsoft CRM 3.0. I am a one man development team who has been tasked with implementing CRM 3.0 with 30 users initially. Our organization has been running on Lotus Notes for quite a while. We moved to echange for e-mail over a year ago but still use Lotus for custom databases. The first step will be pulling the data from Lotus Notes to CRM. I have looked into the Microsoft CRM 3.0 Certification. There is a company that offers a 10 day CRM 3.0 boot camp. Is this a good idea, and at what point should I take it? We would lik...

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

update column
How would I update a column with numeric values so that there are 3 leading zeros for each row? hi it is not possible to add leading zeros to a numeric value. Mathematically, this is redundent and unnecessary. "brian" wrote: > How would I update a column with numeric values so that there are 3 leading > zeros for each row? opps. hit the post button too quick. option 1. custom format if your numeric value is 12345 then see the custom format to 00000000. note. format do not change data - it just changes the way it looks in the cell. option2. format to text then use the c...

Summing Alternate Rows
We have spreadsheets with many rows and need to sum alternate rows. Is there a way to do this without entering a formula using the number of each row? -- KarenQ ------------------------------------------------------------------------ KarenQ's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19608 View this thread: http://www.excelforum.com/showthread.php?threadid=395115 Try this: =SUMPRODUCT(--(MOD(ROW($A$1:$A$18),2)=1),$A$1:$A$18) That formula sums odd-row values in the range A1:A18. To sum even-row values the formula is: =SUMPRODUCT(--(MOD(ROW($A$1:$A$18...

crm 3.0 error 03-01-06
Hello, I'me getting this error while installing crm3.0 for SBS: "error writing to file microsoft.mshtml.dll verify that you have access to that directory" That file is in the C:\Program Files\Microsoft.NET\Primary Interop Assemblies directory. I (and 'everyone') has full access to that dir. What can I do about this?? kind regards, Thomas ...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

Global Column Row Preview Font Size
I know I can change the column, row and preview font size for the current email folder's view, but how do I do it for all of the email folders? I have loads of email addresses each with lots of folders. I don't want to have to do each one at a time. Surely there must be a default font setting (even if it's only in the registry)? Thanks in advance, Tim. I too would love an answer to this. Ian "Timie Milie" <tim_milstead@yahoo.co.uk> wrote in message news:45782ee8$0$27107$db0fefd9@news.zen.co.uk... >I know I can change the column, row and preview font ...

Office 2003 Service Pack 3--subsequent problems opening Publisher
I run Publisher 2003 on Windows XP. On June 13, I updated my system with Office 2003 Service Pack 3 so that I could open Word documents with the file ext docx. Subsequent to the Service Pack 3 installation, whenever I open a Publisher file (which I created), I get the following message: "Publisher has detected a problem in the file you are trying to open. If you are certain that this file came from a trusted source and does not contain harmful information, click OK." What is causing this and is there a way to stop this pop-up message? All publications? Error message when you...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

CRM 3.0 Login Problems
Some specific users are constantly getting prompted for CRM login in Outlook. We are using desktop client (online) online. This happens randomly. We have CRM 3.0 with rollup update 2 and IE7. We have also applied this fix http://support.microsoft.com/default.aspx/kb/934243/en-us. Also added the crm site to local intranet zone. Please help. Thanks. set the authentication in IE check rollup update 2 -- Regards, Imran MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "raj" wrote: >...

Showing 3:54PM instead of 3:54:03PM
How do you get rid of the seconds in the time area. I have changed the formatting in the time. I use the excel file as a data source. I include the time in the mail merge. It always shows up with the seconds in the time. Very frustrating. HELP PLEASE!! TJ it may be formatted as text, so won't respond to changing the time format. if it is text, the TIMEVALUE formula will convert it to a decimal-based time value which can then be formatted by using Excel's normal Number formatting-- to get rid of the seconds. Eddie O "TJ" wrote: > How do you get rid of the secon...

Hyperlink problem #3
I've got two workbooks on a shared drive with hyperlinks linking the two. When a user clicks on the hyperlink on the first workbook, it takes him to the second workbook. Fine. However, when the user clicks on the hyperlink in the second workbook to go back to the first, the error message says that that workbook is already open and it cannot open two files with the same name. Help is appreciated! I just tried a small test in xl2002 and it worked ok for me. I use Insert|Hyperlink to create the links. Are you sure that the hyperlinks point at the file you want--same folder and e...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

Outlook Client #3
Dear All, I have recently installed crm outlook client for one of my users and then also installed the 2 rollups for version 3.0 . Unfortunately outlook is still restarting even after the rollups. Kidnly advise the necessary solution that resolve the problem. Please clarify, outlook loads and then crash "restart"? Has Office applied with latest Office update? Frank Lee, Microsoft Dynamics CRM MVP http://microsoft-crm.spaces.live.com http://www.workopia.com/Links.htm "Faiz Amir" wrote: > Dear All, > I have recently installed crm outlook client for one of my ...

Rounding Numbers #3
I have a list of values as below: 476.14 361.99 345.69 463.08 515.29 403.44 330.68 347.64 375.36 I would like to create a formula that rounds the values to the nearest 0.05 eg. Round 476.14 to 476.15, 361.99 to 362.00, 375.36 to 375.35 etc… Is there anyway that I can do this? Thanks, Jane. JaneC wrote: > I have a list of values as below: > 476.14 > 361.99 > 345.69 > 463.08 > 515.29 > 403.44 > 330.68 > 347.64 > 375.36 > I would like to create a formula that rounds the values > to the nearest 0.05 eg. Round 476.14 to 476.15, > 361.99 to 362.00, 375.36...

How do I insert space between 2 consecutive columns of an XL Shee.
I want to have space between two consecutive columns of a worksheet (of course, without having inserted another column between the two) in order to have separated the Border Lines of the adjacent cells/columns. Please guide me if it can be done in XL. Can you achieve the effect that you're looking for by using a double vertical border down the right side of the left column and having no border down the left side of the right column? Rgds, ScottO "Shamshad Butt" <Shamshad Butt@discussions.microsoft.com> wrote in message news:1222EE13-11A9-4354-9F12-D1F1155D3902@microsof...

SetWindowsHookEx #3
Hi there. Could someone explane to me what is the purpose of dwThreadId, the last member of SetWindowsHookEx function? I've expected that this is a thread id with which hooks is associated. That means that hook is getting only messages produced by this particular thread. But it looks like I'm getting system wide messages anyway. so could somone make it clear for me? In fact I need to process a mesages of only one window. I know the thread id of that window. But! this thread is not in my process! And I don't want a real system wide hook because it slows down the box. Ok, I think ...

RMS 1.2 vs 1.3, plus integrate with Great Plains
1.) I am working on an assessment for upgrading our current RMS software from 1.2 to 1.3. My assessment will take in account the benefits, broken down by store operations (Retail) and the benefits to IT. Also, list risks and potential problems that might be experienced. We currently have 28 stores with 3+ registers per location with another 22 new stores on the schedule over the next 2 years. Are their other in this group of similiar size that have done this upgrade to the new version? 2.) If we do not upgrade the software, can we still integrate the RMS to Great Plains? Would we...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Accessing another user's calendar OL2000/OL2003/Ex2000
Here's the situation: Running Exchange2000, v 6.0, sp4. Users are on XP/OL2003 or NT/OL2000. (we're in the process of upgrading all of the workstations to XP.) VIP user is on XP/OL2003. He can access his calendar just fine on his workstation. His secretary and other office admin type people are still on NT/OL2000. They have reviewer or editor rights to his calendar. They try to access his calendar by chosing File/Open/Other User's Folder... and put in his name and calendar. When they do this, the process hangs. In Task Manager, Outlook is "Running." End the Outloo...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...

WLM transfer to another computer
Hi, I finally moved from Windows 7 RTM to Win7 Pro 64. I did it by installing the new OS on a brand new hard drive, then installed my old hard drive in a 2.5" external enclosure. I've been successful in moving most of my files and settings over, but WLM is the exception. Can someone help answer these questions for me: 1. Where are the actual mail files stored? 2. Where is the account login info stored? 3. In Outlook and OE installing on a new computer, even after moving files, prompted for a full redownload off of the POP server. Anyway to avoid this? Is ther...

Sum a group of numbers
When I first started using Excel, if I had a bunch of different totals on a worksheet, I could hold down the right mouse button and cover the group I wanted to add. The sum of these numbers would appear on the bottom of either the worksheet or a task bar. I have lost the ability to do this. Or I have lost the task bar. How do I get it back? Hi Barbie, That would be the status bar. If it is not showing, go to View>Status Bar to turn it on. If the sum function is still not showing, right click anywhere on the status bar and select which option you want. HTH Martin "Barbie...

copy values generated by conditional formula in one sheet to the other work sheet as values
Hi Everybody, I have data generated by conditional formulae in work sheet1 in columns A to J. If the condition is satisfied the cell will display a realnumber, if the condition doesn't satisfied the cell will display the text"FALSE". Now I wanted to copy the cells which have the real numbers in sheet1 to sheet2 as values(as we do with paste special and paste the values) Do we have any formula or other method to copy the cells in sheet1 to sheet2. can anybody helpme out in this issue. Thanks and Regards Ramana Select your range to copy edit|goto|special|c...