Another Sumif problem

     A             B            C                D                            
      E
1   col	  tot 	subtot1	subtot2                           DISC
2   black	  12  	 1	  11                                  AA
3  white	  14	 2	  12                                  BB
4  blue	  16	 3	  13                                  CC
5  blue	  18	 4	  14D                                DD

QUERY1:	(COLUMN A TO D ONLY, COLUMN E WILL BE USED ON QUERY2)	
8  blue	  34		

query 1 states that if i type black (A8) the response of the B8 will be 34 
using the formula  below ( 16+18)
=SUMIF(A2:A5,A8,B2:B5)

query 1 has no problem
and query 2 will follow.....

but i added one column E (disc)

and as we can see i need to get only the value for column E with CC disc 
ONLY, it should be 16 only. please help and put the exact formula. tnx

=)

==================
-- 
xxxJessexxx
0
Utf
5/8/2010 9:15:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
801 Views

Similar Articles

[PageSpeed] 15

Try this…

Excel 2003:-
=SUMPRODUCT((A2:A5=A8)*(E2:E5="CC"),B2:B5)

Excel 2007:-
=SUMIFS(B2:B5,A2:A5,A8,E2:E5,"CC")

In your example you have mentioned the A8 cell value as Black but I hope 
that it should be Blue.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Jessejames" wrote:

>      A             B            C                D                            
>       E
> 1   col	  tot 	subtot1	subtot2                           DISC
> 2   black	  12  	 1	  11                                  AA
> 3  white	  14	 2	  12                                  BB
> 4  blue	  16	 3	  13                                  CC
> 5  blue	  18	 4	  14D                                DD
> 
> QUERY1:	(COLUMN A TO D ONLY, COLUMN E WILL BE USED ON QUERY2)	
> 8  blue	  34		
> 
> query 1 states that if i type black (A8) the response of the B8 will be 34 
> using the formula  below ( 16+18)
> =SUMIF(A2:A5,A8,B2:B5)
> 
> query 1 has no problem
> and query 2 will follow.....
> 
> but i added one column E (disc)
> 
> and as we can see i need to get only the value for column E with CC disc 
> ONLY, it should be 16 only. please help and put the exact formula. tnx
> 
> =)
> 
> ==================
> -- 
> xxxJessexxx
0
Utf
5/8/2010 9:50:01 AM
Reply:

Similar Artilces:

Problem automating outlook
Hi I am using the following code to automate outlook from within MS Access; Dim O As Object O = CreateObject("Outlook.Application") The problem is that I am getting the following error on the second line; Automation error The specified module can not be found What is the problem and how can I fix it? Thanks Regards Dim O As Object Set O = CreateObject("Outlook.Application") Regards Mark Dormer "John" <John@nospam.infovis.co.uk> wrote in message news:eFs9QqIUGHA.1236@TK2MSFTNGP11.phx.gbl... > Hi > > I am using the following code to...

Word 2003 template header problem
When I opened by 2003 template for a letterhead in Word 2007, I found that the header where the letterhead was, is now blank. What happened? Can I fix this? Thanks! This could be a display issue. Does the header contain a graphic? If so, make sure that "Show drawings and text boxes on screen" is selected and that "Show picture placeholders" is cleared in Office button | Word Options, Advanced category. -- Stefan Blom Microsoft Word MVP "kaoinla" <kaoinla@discussions.microsoft.com> wrote in message news:8AE24B9F-7421-4918-A468-...

Strange problem with local users sending e-mail
We are receiving an error with some users when sending e-mail. It seems to be hit or miss. At times the message will go through other times it won't (This is to the same e-mail address). The user receives a bounce back of: There was an SMTP communication problem with the recipient mail server. <mail.hostname.com #5.5.0-smtp; 550(mail.hostname.com) [xxx.xxx.xxx.xxx:xxxxx] is currently not permitted to> In the above error mail.hostname.com is the FQDN of the local exchange server and xxx.xxx.xxx.xxx is the public IP of the exchange server. Server is behind a firewall and usi...

how do I get Win live mail on another computer
Hi, I have Windows Live Mail on my desktop computer but I can't figure out how to get it on my laptop. I tried downloading it again but it makes a whole new account and have to make a new email addy. What can I do to get my account to laptop. Thank you, Pippa Windows Live Mail does not require one to create any account (mail or news)...press the button that says Cancel. To 'get' your email and news accounts from one machine with WLM to another machine with WLM... Desktop Pc: 1. Ensure WLM's menu is present(click on the small square[upper right of toolbar] ...

A Linking Problem
Assume I have a column of numbers, in a file named Source. (range is B2:B10). These cells are linked to a column in a file named Destination(range is E2:E10). If I add a new row 5 to the source, is there a way I can get the previously linked cells in Destination to continue to display the correct values. After adding the new row 5 in source, rows in Destination below row 5 display values from rows below (in the Source file). I hope this is not too confusing a question (I am confused). If it of importance, the linking was done using Paste Link in the Paste Special dialog. Thanks. Hi PA Yo...

Windows 7 taskbar API problems
Folks The background is that an Access MDE/ACCDE created by a developer is not conceptually the same as an Excel spreadsheet or Word document file created by the user. My Auto FE Updater utility automatically downloads any new version created by the developer from the server to the workstation as well as doing lots of other stuff such as creating shortcuts on desk tops. If this was a user created Access database file which resides in the users My Documents folder, or similar, than they are conceptually the same as a Word or Excel file. The problem is if the user right-clicks on...

Update Query to Change Values From One Table To Another
Hi, I have two tables with a number of fields in each table. In one table called ACCOUNTS CHARGED the yes/no field called CHARGED is always by default "yes" everytime a record is entered in it. I want it every time to update the table called RESPEL ALL CHARGES (when running an Update Query) the identical field CHARGED by looking at the RESNO field in the table ACCOUNTS CHARGED to be the same as the RESNO field in the table RESPEL ALL CHARGES. Thanks ...

Hotmail Problem Please Help
I am running Exchange 2003 Standard on a Windows 2003 Server. I also have ISA 2000 on the same box. Everything is working fine. The problem I am having is getting mail out to a hotmail address. When I send then mail, I see it sits in the Queue for less then a minute then disappears. It never makes it to the Hotmail address nor do I get any return errors back in my client indicating the mail never made it to its destination. On Sun, 13 Nov 2005 15:51:04 -0800, "Kevin Mc" <Kevin Mc@discussions.microsoft.com> wrote: >I am running Exchange 2003 Standard on a Windows 200...

Problems creating a newsletter
I chose the form "Newsletter - email". I have created a 3 page newsletter. Now, I have NO IDEA how to send it out as an email. I do not want to send it as an attachment. I cannot figure out which "save as" format or what I need to do so that i can email this newsletter. Is there a website that goes through how to do this? Am I correct when I say that it has to be html in order for me to send it as email? That is not one of the options. Any help you give me would be greatly appreciated. I am using 2003 Publisher. I created the book "Tri-Puzzles for Genesis - I"...

Bulk Insert Problem
I am getting the following error while trying to use the Bulk Insert Command Msg 4864, Level 16, State 1, Line 6 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (PT_ID). Msg 4864, Level 16, State 1, Line 6 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 15 (CREDIT). Msg 4864, Level 16, State 1, Line 6 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 14, column 13 (PAY). Msg 4864, Level 16, Stat...

Form printing problem
Hi all, I have a form that holds customer records which we have been printing out for filing. I put a print record command button at the top of the form and this has been working perfectly for at least the last four years printing on plain paper using auto selection on the printer. Recently the form record has started printing onto header paper. It doesn't matter whether I change the properties on the printer it keeps printing to headed paper and the only way round was to put plain paper into the headed paper tray. We have now a new printer in the office which has been set as my defa...

VC++ project setting strange problem
project setting string size - strange problem Hi all. Am facing a strange problem. In my project setting, I have lot of .h file directories in my additional include directories, and I have lot of preprocessor definitions also. I compiled the code, fine perfect, close the workspace, reopen it and checkd the project options , everything fine. Now I have to add one more preprocessor definitions.Same way, I compiled the project, it is fine. Now I close the worspace,reopen, the last one I added is got cut by some alphabet. Now my doubt is, do we have any max project setting string leng...

MS06-029 problems... (Send As rights)
Hi, We have Exchg. 2003 SP2 with installed patch MS06-029 (KB912442). This patch caused an Send As rights on Mailbox Store, so as well we have to put Send As rights on A.D. user by user!!! All of users are on the same Mailbox store, but we have to change A.D. persmission. Microsoft says: If you are using Microsoft Exchange Server 2003 Service Pack 1 (SP1) or Microsoft Exchange Server 2003 Service Pack 2 (SP2) and you install this security update, third-party services such as BlackBerry or GoodLink may be affected. Shared mailboxes and Microsoft Entourage delegation scenarios may also b...

Offline address lists Problem
Hi, I´ve a exchange 2000 clustered enviroment in the company, but outlook users didn´t syncronize offline address list, they receive a error. Everything it´s ok in the environment, I configured a server to host the offline address list and the DC´s are OK. What can be the problem? Thanks! -- Gustavo Z. Velozo ...

signature and font problem reading pane too
I made a change to my outlook by sending an email which changed my reading pane and adds mb to the message size. A simple 3 line message without signature can be 1 mb or larger. Others cannot open them. They stall my computer. Some incoming mail will not view at all. I can hit reply and see the text that is not visible in the reading pain. I started having problems with the signature not giving me a space above it to type. In trying to fix this something happened to the formatting of outgoing mail and viewing it in the reading pane. It underlines as a default? Help It...

Problem downloading old messages left on server
I have been having an intermittent problem where entourage downloads all the messages left on my Internet service provider's server, even though they have already been downloaded. I have a pop account. It only happens once every month or so. The service is a small private company called Hawaii Broadband. It is only happening with the Hawaii Broadband account. (I have two other email accounts). But the folks at Hawaii Broadband have no idea why this might happen and have no suggestions. I am using Entourage 2004, but my partner is using Entourage 2008 and he is having the same ...

Synchronization problems with CRM 1.2 in Outook
Hi Everybody, I have been encountering some problems synchronizing my Outlook installed with CRM client 1.2. The synchronization will fail whenever I try to sync. I can't take my information offline. Can anybody help me out with this?? Thanks Jeremy Please give us more information such as any error messages you get "Jeremy" <jeremy_x25@yahoo.com> wrote in message news:a60801c4356e$f90cb210$a301280a@phx.gbl... > Hi Everybody, > > I have been encountering some problems synchronizing my > Outlook installed with CRM client 1.2. The synchronization > will...

quiry adding another field calculated from the existing fields
if I have a table of transactions on a bank account with two fileds: Field_1 holds the date of the transaction Field_2 holds the amount of the transaction What would be the quiry which would add the third filed Field_3 containing the balance on the account which is calculated as the value from the previous line of Field_3 less Filed_2 from the current line? You've no need to store the balances in a column in the base table, nor should you do so as it introduces redundancy and the risk of inconsistent data. The balances can be computed on the fly in query like so: SELECT Field_1, ...

XSL choose problem
Hi all I've not used XSL before and have been asked to change an existing file. An extract from the original is: (white space added for ease of reading) ....<xsl:template match="Trip"> <table width="560" border="0" cellspacing="1" cellpadding="0" align="center"> <tr valign="top" height="7"> <td colspan="4"> <img src="/graphics/global/spacer.jpg"/> </td> </tr> <tr valign="top" height="12"> <td colspan="3" alig...

Problem when exporting Excel => MS Project (2002)
Hello, I make an export from Excel to MS Project (2002), the concern is that MS Project has a logic of data processing, it begins to change my data in that logical I'd like to know how to disable this change when exporting? Thank you very much. -- aelmalki ------------------------------------------------------------------------ aelmalki's Profile: http://forums.techarena.in/members/147422.htm View this thread: http://forums.techarena.in/microsoft-project/1275770.htm http://forums.techarena.in Hi, Most calculations can be "avoided" when in MS P...

Workflow Post URL problem
Hi, we would like to use Post URL action in workflow. it gives following errors in the event log. We try different urls even without posting any items. it does not work. any suggestions will be appriciated Error: action {7A37BE8A-CFF7-4FD1-97C4-55E09A118B51} failed in step {A0D771F2-4B5B-46B2-BE42-54EC6B3FF418} of process instance {90ED90DF-2587-4FCD-895D-8375F8BF67D1}. <description>Error in action posturl.</description><details>Failed to connect the url http://localhost/PostURLWebAppl/PostURLQueue.aspx</details><file>d:\crm\build\3297\src\platform\workf...

Paycheck problem
Hi, there. I'm new to this group. My name is Daniel, I'm from Brazil and I use MS Money since 1999. But I've always had some problems with it. Now I'm using Money 2007 DeLuxe, and, besides the "% of income" display problem (that seems to be a bur, really), I'm having some issues about paychecks. Here we go: Let's assume my total income is 1000.00. My employee deposits me 400.00 every 10th day, and the other 600.00 every 25th day. My paycheck says the following: "Wage: 1000.00 Deduction: 400.00 Deposit: 600" Now, talking about Money: I have a depos...

billionton bluetooth adptar driver problem
:huh: hi looking for billionton bluetooth usb adptar 100mt class1 driver for vista 32b. please some body help me for that ..............[image: http://forum.gsmhosting.com/vbb/../ubb/eek.gif] thanks -- nelbulani ...

Alternate Recipient Problems
I have an interesting observation that I don't know the explaination for it. This is my configuration and history: 1. One Site, Two Exchange 5.5 SP4 servers 2. OLD_SERVER IMS service was disabled after we put in the new server. 3. NEW_SERVER has just recently been put in. Our MX records were rerouted to the NEW_SERVER IP. IMS service is running and our IN/OUT Internet mails are going through this one just fine. Problem 1. "Alternate Recipient" only works for emails sent from mailboxes located on the OLD_SERVER and the NEW_SERVER 2. When emails sent from an Internet User (su...

Problem with Report code
Here is my code Private Sub Report_Activate() If Me![rptCReDriving Licence].Report.Recordset.RecordCount = 0 Then Me.lblnoinfo.Visible = False Else Me.lblnoinfo.Visible = True End If Me.Refresh End Sub I have a main report with a number of subreports. in the above example if the rptCREDriving Licence has no data I want lblnoinfo (which is an unbound label) to appear in its place. However it doesn't work. can anyone see why? Thanks Tony Hi Duane tried your second suggestion and got a prompt box for the name of the report and Access changed the control source to this =IIf...