Summing Values using multiple criertia

Does anybody know a formula I could use to sum a range of 
values based on multiple criertia? 

Example:

Division   Type    Wage
Bulk       Driver  200.00
Bulk       Admin   400.00
General    Admin   500.00
Bulk       Driver  100.00

I want to sum the wages for Divison "Bulk" & 
Type "Driver". How can I do this???

Thanks!
Jane
0
anonymous (74722)
9/28/2004 3:14:27 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
350 Views

Similar Articles

[PageSpeed] 12


  =SUMPRODUCT((A2:A4="Bulk")*(B2:B4="Driver")*(C2:C4))

If there are lots of such totals, you may want to consider a pivot table
rather than formulas.

On Mon, 27 Sep 2004 20:14:27 -0700, "Jane"
<anonymous@discussions.microsoft.com> wrote:

>Does anybody know a formula I could use to sum a range of 
>values based on multiple criertia? 
>
>Example:
>
>Division   Type    Wage
>Bulk       Driver  200.00
>Bulk       Admin   400.00
>General    Admin   500.00
>Bulk       Driver  100.00
>
>I want to sum the wages for Divison "Bulk" & 
>Type "Driver". How can I do this???
>
>Thanks!
>Jane

0
anonymous (74722)
9/28/2004 3:33:23 AM
Hi!

Try this:

=SUMPRODUCT(--(A11:A14="BULK"),-(B11:B14="DRIVER"),C11:C14)

Biff

>-----Original Message-----
>Does anybody know a formula I could use to sum a range of 
>values based on multiple criertia? 
>
>Example:
>
>Division   Type    Wage
>Bulk       Driver  200.00
>Bulk       Admin   400.00
>General    Admin   500.00
>Bulk       Driver  100.00
>
>I want to sum the wages for Divison "Bulk" & 
>Type "Driver". How can I do this???
>
>Thanks!
>Jane
>.
>
0
biffinpitt (3172)
9/28/2004 3:37:24 AM
Hi, Biff:

Don't you need two minus signs in front ot the 2nd criterion, too?


On Mon, 27 Sep 2004 20:37:24 -0700, "Biff" <biffinpitt@comcast.net> wrote:

>Hi!
>
>Try this:
>
>=SUMPRODUCT(--(A11:A14="BULK"),-(B11:B14="DRIVER"),C11:C14)
>
>Biff
>
>>-----Original Message-----
>>Does anybody know a formula I could use to sum a range of 
>>values based on multiple criertia? 
>>
>>Example:
>>
>>Division   Type    Wage
>>Bulk       Driver  200.00
>>Bulk       Admin   400.00
>>General    Admin   500.00
>>Bulk       Driver  100.00
>>
>>I want to sum the wages for Divison "Bulk" & 
>>Type "Driver". How can I do this???
>>
>>Thanks!
>>Jane
>>.
>>

0
anonymous (74722)
9/28/2004 3:40:44 AM
Ooops!

I think that was a result of a line wrap that I tried to 
correct!

Yes, the double unary is needed.

Biff

>-----Original Message-----
>Hi, Biff:
>
>Don't you need two minus signs in front ot the 2nd 
criterion, too?
>
>
>On Mon, 27 Sep 2004 20:37:24 -0700, "Biff" 
<biffinpitt@comcast.net> wrote:
>
>>Hi!
>>
>>Try this:
>>
>>=SUMPRODUCT(--(A11:A14="BULK"),-
(B11:B14="DRIVER"),C11:C14)
>>
>>Biff
>>
>>>-----Original Message-----
>>>Does anybody know a formula I could use to sum a range 
of 
>>>values based on multiple criertia? 
>>>
>>>Example:
>>>
>>>Division   Type    Wage
>>>Bulk       Driver  200.00
>>>Bulk       Admin   400.00
>>>General    Admin   500.00
>>>Bulk       Driver  100.00
>>>
>>>I want to sum the wages for Divison "Bulk" & 
>>>Type "Driver". How can I do this???
>>>
>>>Thanks!
>>>Jane
>>>.
>>>
>
>.
>
0
biffinpitt (3172)
9/28/2004 3:49:29 AM
Reply:

Similar Artilces:

Switching between multiple open windows
I ran across an option to change how to switch between multiple open windows on my desktop. It was an option to choose between hovering over a window or clicking on it. Thinking that only having to hover over a window to switch to it would save time, I selecting the option to hover... big mistake as it just became a big pain in the a_ _ for me. Now I would like to change back to one-clicking to change between viewed windows but can't remember or find where the option is located. Any help will be appreciated. -- rushl On 2/03/10, rushl posted: > I ran across an opti...

Select last value
I am trying to select the last (bottom) value on a one-column list. I am using the COUNT function to designate the bottom value that is not zero, and the CHOOSE function to select the designated value. But, I can't make that work. Help appreciated. try =match(a number larger than possible,your range) -- Don Guillett SalesAid Software donaldb@281.com "Carl" <c@invalid.com> wrote in message news:eciLEKsvFHA.3236@TK2MSFTNGP14.phx.gbl... > I am trying to select the last (bottom) value on a one-column list. I am > using the COUNT function to designate the bottom va...

ProbleM: when I restore a mailbox using Exmerge with a pst file, nothing is transferred.
Hi, I am practising Exmerge for a big remote site migration in a couple of weeks. One thing I dont understand is that I can backup one test mailbox fine using Exmerge (I know this works, as I have opened the mailbox pst file within outlook and everything is there), but when I perform the restore using the pst file, nothing happens. There is no error messages, and Emerge goes through the motions (though it finishes supsiciously quick), but when I open the mailbox, no emails have been restored. Although it is great that Exmerge is working for the backup part of the stage, I am disappointed it i...

Excel-Multiple Cells Being Hi-lited
Sometimes when I'm setting up a worksheet and I left-click in a cell, multiple cells in the same column are hi-lited. After it happens the first time, it continues as I move through the worksheet, reducing my ability to get work done considerably. After some trial and error, it seems to occur when I've been adding and/or deleting columns and/or rows, after a header has been installed. I can move throughout the worksheet using the arrow keys, but it is a time consuming and cumbersome technique. I think the version I'm using is Office Professional 2007 (file extensi...

Old emails not displayed (Multiple PC's using same inbox)
I have 2 machines running XP and Outlook 2000. Both mahcines are setup to using the same Exchange Server email account. When i open outlook in one machine all mail are visable is the inbox, and new mail is received correctly. But if i opne Outlook on the second machine my inbox is empty. Any new mails will appear quickly then disappear. I cannot get the second machine to display old emails. If I leave the first machine off, all new mail will stay in the second machines inbox, but if I open outlook on the second machine all mail disappears and is shown in the first machines inbox. A...

CtreeCtrl multiple selection
Hi, Is there any simple way (samples) to set a CTreeCtrl has multiple selection feature? Thanks, Chi Try these: http://www.techsoft.no/bendik/ http://www.codeguru.com/Cpp/controls/treeview/misc-advanced/article.php/c723 http://www.codeguru.com/Cpp/controls/treeview/misc-advanced/article.php/c629 -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Chi" <anonymous@discussions.microsoft.com> wrote in message news:21bd01c4aa63$2e355d70$a501280a@phx.gbl... > Hi, > > Is there any simple way (samples) to set a CTreeCtrl has > multiple selection feature? Thanks, > &...

Switching companies using SQL Passthrough
I have an application that uses SQL_Passthrough. As part of the code you must execute a statement that uses the appropriate database. The code looks like this: set SQL_Statements to "use MYDB"; status = SQL_Execute(SQL_connection, field SQL_Statements); This works fine, but my application can be used for any number of databases. At first, I modified it to use the Dex.ini file, which works. Here is the modification: dbname = Defaults_Read("SQLDB"); dbopencommand = "use " + dbname; set SQL_Statements to dbopencommand; status = SQL_Execute(SQL_connection,...

Using Word 2003 in Vista: Opening dictionary shuts down Word
This is a problem in Vista; it did not occur when I used Word 2003 in XP. Whenever I try to open the dictionary in Word 2003, either by clicking its icon, or hitting Alt+click over a word, Word shuts down. Vista Business, Service Pack 2 Thinkpad T400, Intel Core 2 Duo CPU, 3GB RAM ...

How to sum the 3 best scores for every country in a contest?
To sum the 3 highest numbers, following functions can be used in a really nice combination: =SUM(LARGE(A1:A100, {1,2,3} ) ) But how to add an condition to it? Example: Column A below are the scores for induvudual persons in a contest. Column B below is the country they are competing for .. How to add the sum of the 3 best scores for each country ? The correct answer should be 213+199+187=599 for USA and 198+158+135=491 for Canada. A B 141 USA 199 USA 135 CAN 187 USA 158 CAN 145 USA 213 USA 198 CAN 120 USA 180 CAN Reall...

Using namespaces? I've some messy nested contexts that I want to clean up...
Hi, I've got the following code structure Class A { ... private: Class B { public: enum C { ENUM X } C MyVar; C MyFunc() } } So for function definitions in B I have to write A::B::C A::B::MyFunc() and for objects of B in A if(pb->MyVar==B::ENUM_X) It's all just a bit messy. Isn't it. Someone please help. Regards. ...

IV40100.MAINLOCN
I am trying to determine where the contents of IV40100 (Inventory Control Setup) are determined. I have ocated most of them from the Inventry Control Setup window/form, but cannot locate where the contents of the following fields are set: MAINLOCN (Main Location) DISABLEAVGPERPADJ (Disable Avg Perpetual Valuation Adjustments) DISABLEPERPADJ (Disable Perpetual Valuation Adjustments) Does anyone know from what where these values are set without using Query Analyzer, or are these fields that are not currently being used by Dynamics-GP? The resource descriptions indicate tha...

multiple users...again
My wife and I have road runner cable. We each have our own email account. We want to use Outlook 2003 to read our respected emails. I've tried to configure it so that we each have our own password for outlook for privacy of email. As it stands now, when I click on the Outlook shortcut on the desktop it asks for my password. I give it and it opens up my email folder. I tried to create a folder for my wife's email. I put a password on her folder. Now when I open outlook from the shortcut on the desktop, it want's my password. The program opens and then to get to her f...

Formula to process 3 cells using IF statements
I have 3 columns of experimental data (C:E). Row 30 contains the sums (C30:E30). I need a formula that will examine the three sums and return the column number that has the lowest sum. If more than one column is lowest, select one randomly. Example: C30 D30 E30 Result 10 11 12 1 (C) 22 20 21 2 (D) 32 31 30 3 (E) 40 41 40 Randomly select 1 or 3 51 50 50 Randomly select 2 or 3 60 60 60 Randonly select 1, 2, or 3 Can this be done with IF statements or do I need to write a macro? Well, this is a bit cumbersome, but it se...

Pointing to correct macro path using excel custom toolbar
I have created an excel 2000 template (.xlt) containing a number o macros. When I open copies of this template on various pcs, the macro function correctly, except I cannot successfully run the macros usin the custom toolbar I created, because (I think) within the toolbar th paths to the macros are pointed to the original location on my pc. An advice on how I can resolve this would be gratefully received -- Message posted from http://www.ExcelForum.com Have you thought about building the toolbar when the file opens? Or maybe separating the worksheet portion of the template from the code pa...

Entire Visio page moves whenever I use the directional arrow keys instead of the object I've clicked on
Gurus, Running Visio 2003. For some reason lately, whenever I click on an object and try to move it using the right, left, or up or down arrow keys, the whole page moves instead of the object I've clicked on. This is really annoying! It didn't used to be this way. I'm not sure what I changed. I simply want the object I've highlighted to move whenever I use the arrow keys not the whole Visio page itself! -- Spin On Fri, 24 Oct 2008 17:29:14 -0400, "Spin" <Spin@invalid.com> wrote: >Gurus, > >Running Visio 2003. For some reason lately, whe...

Microsoft publisher, how do I set it to show multiple pages?
I'm trying to show more than one page in a single viewing pane. As in for the use of a banner. You'd think that'd be a simple and accesable function. I don't see it anywhere... Print preview has this function. Not sure if this is what you are asking... -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "BrickShort" <BrickShort@discussions.microsoft.com> wrote in message news:B42E0DAE-9DB6-4587-A157-3C2F5225FA02@microsoft.com... > I'...

how do i enter data for a # of years using a formula?
i am working on excel and the book asks that i enter data s=using formulas for specifically the last three years of what i am referencing to. and i have to know how to us the copy command button. can anyone help ...

multiple vendors or buying groups
Hello: I have a PO that may eventually be charged, by percentage to more that one vendor. So, they are buying a product that is say 25% to one vendor and 75% to the other. How can we do this? Any suggestions? Additional software? VB? Please let me know. -- Kate Hulit Asyma Systems Inc. ...

Supporting Multiple screen resolution
Hi How i can support multiple screen resolution with MFC? Thanks, Ritu What does this mean? Are you asking about dual monitor support? -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Ritu chawla" <rituc@momentum-tech.com> wrote in message news:u$xGo0GbDHA.2404@TK2MSFTNGP10.phx.gbl... > Hi > How i can support multiple screen resolution with MFC? > Thanks, > Ritu > > Hi i am working on an application whose GUI becomes completely visible in 1024 x 800 size only. i need to give support or 800 x 600 also. how can i do this. rgds, ritu "Ajay Kalra"...

.NET 3.5
Hello, Does anyone of you know how I can force vb.net to remove the handle of a local DLL which I used beforehand with Reflection. Here is a fragment of the code. I have a base class and a derived class. This app only knows the base class and creates an Instance of the derived class by using Reflection which works fine. The only problem I have is that the local DLL file is blocked (I can't delete it) as long as I don't close the Application Dim GetCustomModule as BaseClass Dim dll As Assembly = Assembly.LoadFrom("c:\tools\DerivedCass.dl...

GP ver 10 SP 2
Hi Folks I am testing in the Fabrikam company and I have been able to duplicate an error that is happening at my client in their live data. I capture a PO for stock code 100XLG for a qty of 2 at an extended cost of ..25c. The system displays the .25c as the extended cost but it displays ..26c as the 'Remaining PO Subtotal' value. The PO on 'blank' form also prints up a total of .26c. It's fine if I use an extended cost of .24c or .26c and I understand that as the maths division works out fine. Does anyone have a solution to this problem please? Thankx in advan...

Multiple Forest Solutions
Hi All, I have a situation where a client is dealing with 2 seperate Win2K3 forests. What type of solutions are available when there is a fully transitive trust relationship between forests and two seperate Exchange orginizations? Some of the things they want to do is share calanders between the forests, share address books, ect. Any help would be welcome! TIA Paul Identity Integration Feature Pack (IIFP) to share the address books. Inter-Organization Free/Busy Replication Tool to share free/busy. If you google them, you should be able to find scenario walkthroughs, in addition t...

Ex 2003 IS backup, using NTBackup 5.0
On Win2K SP4, I've upgraded from Ex 2000 ==> Ex 2003. I use NTBackup to back up the IS. The NTBackup is version 5.0. Backup proceeds normally; on differentials I can see NTBackup counting log files, etc. However, I tested a restore, and when I catalogued the *.bkf file, NTBackup says "No files were found". So I have an error-free backup, from which (apparently) I cannot restore. Is the Ex 2000 ==> Ex 2003 upgrade supposed to upgrade NTBackup as well, to a version that can handle restoring Ex 2003 IS? Does anyone have an insight into why NTBackup doesn't want to ...

Option trades not using "cash" account
I am using the trial version of Money 2006 Premium and am seeing a problem which also occured in Money 2003. When opening or closing an option position in an investment account, the "Transfer to:" field is left blank by default even though a "cash portion" is defined for that account. This is a minor annoyance in that, for each of these transactions, I need to select the appropriate cash account. In some cases, if the "Transfer to:" field is left blank, Money will ask for the cash account name but this does not always happen and the security is bought or...

Off-Topic: Newsgroup Readers/Viewers
I know this is off-topic, but I couldn't find a newsgroup that was appropriate for this post (like a lounge for MVP's or something)...so I apologize in advance. I currently use Outlook Express (or Outlook Newsreader : "C:\Program Files\Outlook Express\msimn.exe" /outnews) for viewing newsgroups. While its a nice program, it does have a few shortcomings, not the least of which is its difficulty in truly tracking and reporting responses to posts made by the user. I figured since a lot of MVP's and others on these newsgroups tend to respond to a lot of newsgroup posts, an...