Sumif - multiple criteria

If this is a sample of my spreadsheet, how do I calculate so that the results 
will look at both col A and B and sum the total in col C for each variable in 
col. A.  In other words what is the total amount for A for Jan, Mar... 

Col A         Col B       Col C         Col D
a	Mar	 254 	 850 
a	Jan	 266 	 125 
c	Feb	 987 	 121 
c	Jan	 885 	 151 
a	Mar	 352 	 850 
b	Jan	 558 	 454 

Thanks,
Anat
0
Anat (19)
7/13/2005 11:41:02 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
827 Views

Similar Articles

[PageSpeed] 33

A pivot table would give you the desired results.

-- 
Damon Longworth

Don't miss out on the 2005 Excel User Conference
Sept 16th and 17th
Stockyards Hotel - Ft. Worth, Texas
www.ExcelUserConference.com


"Anat" <Anat@discussions.microsoft.com> wrote in message 
news:E027840A-185F-4D47-9C80-756019D82A63@microsoft.com...
> If this is a sample of my spreadsheet, how do I calculate so that the 
> results
> will look at both col A and B and sum the total in col C for each variable 
> in
> col. A.  In other words what is the total amount for A for Jan, Mar...
>
> Col A         Col B       Col C         Col D
> a Mar 254 850
> a Jan 266 125
> c Feb 987 121
> c Jan 885 151
> a Mar 352 850
> b Jan 558 454
>
> Thanks,
> Anat 


0
dlongwth (59)
7/14/2005 2:01:40 AM
Reply:

Similar Artilces:

Sumif with an array for criteria
Hello, I am trying to write a sum if array formula that will look up against multiple criteria and then finally compare to an array...here is an example of what I wrote: {=SUM(IF(Sheet2!$A$12:$A$10000=Sheet3!$B7,IF(Sheet2!$C$12:$C$10000=(Lots!$H$6:$H$18),Sheet2!$K$12:$K$10000,0),0))} The formula works fine if I take out the $H$18, but as soon as I try to match that, it will not work... So to clarify what I am trying to do: Sheet 2 contains my data: Column A is weeks - Column K is Sales - (what I want to sum) Column C is SKU ID Sheet 3 is the Form I am summing to. Cel...

saving multiple sheets in a workbook
I have 1100 worksheets in a workbook all named after what I need to save them as, but I can't find any kind of "bulk save" where Excel can take each of the sheets and save them as individual files vs saving them all in a workbook. I don't want to click on "Save as" for each of the files, then manually enter the name of the worksheet, especially since there is no hotkey assigned to "save as". Does anyone know of a way around this? Or perhaps a shortcut for doing it? Hi Try http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.ro...

View multiple calendars in one
I want to create multiple calendars in one instance of Outlook. I then want to have a calendar that consolidates all entries in all the other calendars. Any ideas if/how this is possible? Thanks, Tim Creating sub-calendars is easy. Consolidating them all onto one is hard. I wouldn't do it that way myself, instead I would use categories and views. Have a single calendar and create categories for the various sub-calendars you want to have. You can then use custom views to show you only the "calendars" you want to see at any given time. -- Aloha, -Ben- Ben M. Schorr, OneNot...

Values in multiple columns into one column
Hi everyone, I have a spreadsheet with values in multiple columns where for instance col A is blank colb or c or d won't be...there will be something in one where the others are empty. I need to create a column with all the values together. Tried using the following formula but it doesn't seem to work... the values are all dates.. =IF(O2=" ",N2,IF(N2=" ",K2,IF(K2=" ",J2,O2))) Can anybody help!! Cheers Dan Hi try =IF(O2="",N2,IF(N2="",K2,IF(K2="",J2,O2))) >-----Original Message----- >Hi everyone, > >I hav...

Counting Individuals NOT Occurrences with than one criteria
I have a Excel 2007 Spreadsheet with 26 columns and 190 rows arranged as follows: A B C D E F 1 Harm Red Fully Ach Abstinence Partly Ach Harm Red Fully Ach 2 Harm Red Fully Ach Harm Red Fully Ach 3 Harm Red Partly Ach 4 Abstinence Not Ach Harm Red Partly Ach 5 Abstinence Partly Ach 6 Harm Red Fully Ach Harm Red Partly Ach 7 Harm Red Fully Ach Now I've been trying to use SUMPRODUCT and it only gives occurrences of say 'Harm Red' and 'Ful...

Outlook & Multiple SMTP
I'm trying to set up outlook 2000 to allow the user to select between different SMTP addresses when sending a mail. In 2002, I have it set so that when I write a new message, I can choose the account that's sending (and if I don't choose it uses the default account.) I have a friend using Outlook 2000 and she has 3-4 email accounts set up, but she has no choice in which account the outgoing mail is sent from. Does it have anything to do with Internet Only vs. Corporate installation? Thanks Yes - you want IMO mode for multiple smtp setup. "Jane" <noel_x...

Multiple columns into 0ne
Hi, We get a lot of information that comes into different columns, say Product 1 - 88. We never know how many values there are in every colum or how many Products there are Now we want a macro that puts all the values into one column, say A - can be in sheet 2. We do not want empty rows or columns but it is very important that the figures are in the original order. Regards More information is needed. Is there a header row? If so, what do you want to become of it? Might the be empty cells within the table? How do you recognize how many columns are used, or is the first row alw...

Refreshing Form with Multiple Forms and Fields
I have a main screen form that allows entry to start from that location. It has four subforms and some other fields that are picked from other locations to filter the record. My challenge is it does not Refresh right away. I have tried putting Refresh and Repaint etc in the main form code but it still doesn't refresh. I put a Refresh button on it and that works but it's slow. Can anyone tell me what would be a more efficient method to get this screen to refresh right away. Thank you, ...

Consolidate multiple investment accounts?
I have a question that I can't seem to find the answer for anywhere. I have a Roth IRA with American Funds that consists of 4 funds. When I connect to American Funds during account setup, instead of creating one account for the Roth IRA, I get 4 accounts...one for each fund. Is there a way to consolidate all of these individual fund accounts into one overall Roth IRA account? At first, this wasn't a big deal, but my wife and I both have a Roth IRA with American, and my wife just added a 401k rollover with American. As a result, we have 15 individual "accounts" inst...

Multiple Records 1 Field
I need to create a report where I consolidate multiple records from 1 table and join them to 1 record in another. The problem is, I need them to all be joined into 1 record. tblStudents (Name, LocID, Grade) - Single record per student tblMods-Math (ModID, LocID, Mod) - Multiple mods for 1 kiddos is normal Finished product needs to be SomeQuery (Name, LocID, Grade, Mod1, Mod2, Mod3, Mod4, etc...) How do I do that? I tried creating a crosstab query, but that didn't work out. -- Thanks As Always Rip Rank the Mod values, per LocID. One possible solution is: SELECT a.locID, a.mod, COU...

How can I view files chronologically when opening multiple files
I often open multiple invoices for the same account, and I want to view them beginning from the oldest to the most current. However, Excel situates them in reverse order such that when I close one or do "ctrl tab" to step to the next file it always steps to the newest file. I would prefer the "stepping" begin and proceed with the oldest files. I don't know that we have any control over the order Excel opens workbooks or stacks their windows. You can press Ctrl-Shift-Tab to go in the reverse order though. -- Jim "Stevilsize" <Stevilsize@discussions...

Multiple modal dialog or ::MessageBox, crashes the application
Hi, We have an application, in which we need to show multiple modal dialog boxes at the same time. But, when we close these modal dialog boxes, the application crashes. The messagebox is invoked as shown.. ::MessageBox(hWndParent, m_strMsg, g_strTitle, MB_TASKMODAL | MB_SETFOREGROUND ); Here is the call stack.. 524.84c): C++ EH exception - code e06d7363 (first chance) (524.494): Access violation - code c0000005 (first chance) First chance exceptions are reported before any exception handling. This exception may be expected and handled. eax=001804ee ebx=00fb4cd0 ecx=0012d5f8 edx=7ffeff0...

Sumif #2
I'm finding @sumif is not summing all numbers in a range. Does the=20 criteria have to be in a certain format? I believe I've fixed all = format=20 discrepancies. Any other suggestions? Regardless of format, if some of your numbers were entered as Text, then they remain text and won't be counted. To coerce text numbers to real numbers, copy an empty cell, select your numbers, choose Edit/Paste Special, selecting the Values and Add Radio buttons. In article <088001c392c7$f86bfe00$a101280a@phx.gbl>, "Sum Problem" <anonymous@discussions.microsoft.com> wr...

counting multiple columns
Hi, I'm using the following formula(s) to count how man instances of a certain number I have in three different columns. The problem is that it is working for most of the numbers but not for others. Only the numbers 1-12 are in the cells. EX. =COUNTIF($N$5:$N$34:$P$5:$P$34:$R$5:$R$34,2) =COUNTIF($N$5:$N$34:$P$5:$P$34:$R$5:$R$34,4) Here are two examples. The top forumula is for counting the number of cells in the three columns that contain the number 2. The bottom formula does the same thing for the number 4. I get the right answer for the "4" formula, but for the "...

Remove Multiple items from a Site
version 7.50g30 Is there a way to remove multiple items from a site all at once? I have been using the Item Quantities Matenence window, but can only do one item at a time. I have several hundred items to remove. Thank you I've done it with a macro, but I agree it should be part of the software...if you can add by ranges, why not delete? -Go to Tools/Macro/Record -give the macro a name -record yourself deleting one (including pressing the next arrow at the bottom, if they're in sequence) -go to Tools/Macro/Stop Record You can either then run it via Tools/Macro/Play (or Ctrl-F8)...

Printing multiple worksheets in 2000
How do I print multiple worksheets as one print job in Excel 2000? Hi Mary Select the Sheets you want(hold the Ctrl key when you select them) and print Don't forget to ungroup Or use this macro lines ActiveWorkbook.PrintOut 'the whole workbook Worksheets.PrintOut 'all worksheets Sheets.PrintOut 'all sheets Sheets(Array("Sheet1", "Sheet3")).PrintOut 'all sheets in the array ActiveWindow.SelectedSheets.PrintOut 'print all selected sheets ActiveSheet.PrintOut 'only the activesheet Sheets("Sheet1").PrintOut 'only "Sheet1...

saved as single page now need to convert to one large multiple pag
i have saved several individual pages as single files in publisher now i need to convert all of these individual saved files back into one multiple page document cut and paste does not work how do i do this please help kristy wrote: > i have saved several individual pages as single files in publisher > now i need to convert all of these individual saved files back into > one multiple page document cut and paste does not work how do i do > this please help ================= Which version of Publisher? Why doesn't copy/paste work? Maybe the following link will offer some ide...

Money SB 2004 and multiple companies?
Hi. Question for anyone. I'm an independent small business owner and own 3 different small companies. Can MS Money SB 2004 support multiple companies? Not in one Money file. You will need 3 different files. DAE >-----Original Message----- >Hi. Question for anyone. I'm an independent small >business owner and own 3 different small companies. Can >MS Money SB 2004 support multiple companies? >. > "Doug Edwards" <dedwards@dellmail.com> wrote in message news:0b5901c35b57$8cd1fc00$a601280a@phx.gbl... > Not in one Money file. You will need...

Multiple filters using combo box
Hi, I'm trying to figure out how to filter a query using the following criteria: Start Date and End Date Plus Issue or All Agent or All Rep or All In each combo box I have the following: SELECT [tbl_Issue].[Issue] FROM tbl_Issue union select ' All' FROM tbl_Issue ORDER BY [Issue]; etc. In the query I have the following: Date: >=[Forms]![frmselector]![txtStartDate] And <=[Forms]![frmselector]![txtEndDate] Issue: Like [Forms]![frmselector]![Issue] etc. However, I get a blank query. What am I missing? I suggest you download a sample database that uses ...

Combining multiple columns of text
I have a file of addresses. Each individual line of the address is in a different adjacent cell. I would like to be able to combine the text into one cell that is separated by commas, and or spaces. Is there an easy way to achieve this -- Amber Are you making labels? If yes, then keep your data the way it is. It's much easier to combine the values than to separate it. Use excel as your data base and use MSWord as the application to make the mailing labels. You may want to read some tips for mailmerge. http://www.mvps.org/dmcritchie/excel/mailmerg.htm http://www.mvps.org/word/F...

HELP! Procedure or Program suggestion for finding multiple - the last 4 occurances
This is a multi-part message in MIME format. ------=_NextPart_000_0009_01C67D09.E7F4DE00 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I need help in finding a procedure or program suggestion for finding = multiple - the last 4 occurances - of recurring appointments in Outlook. The standard "find" seems to have a bug in that it will not find all = occurances of recurring appointments, it will not find the last series = of a recurring appointments -i.e. the last months worth that has already = occured - and in general returns ...

Multiple Trigger Firing
We have a client who is claiming that a trigger is firing multple times. We are setting up the trigger at Startup with the following code. ret = Trigger_RegisterFocus( anonymous('OK Button' of window 'UPR_Calculate_Checks' of form 'UPR_Calculate_Checks'), TRIGGER_FOCUS_CHANGE, TRIGGER_BEFORE_ORIGINAL, script OurScript); As you can see, this trigger is registered to fire on the OK Button click on the Calculate Checks screen. The only thing that happens in OurScript is a connection to a database, a call to a stored procedure, and the c...

Update field (multiple criteria and updates)
I would like to update 2 fields with the following Where [item description] like "*BASIC*" update [item description] = "BASIC" and [item] = "HITS" Where [item description] like "*FARE*" update [item description] = "FARE" and [item] = "HITS" Where [item description] like "*SEARCH*" update [item description] = "SEARCH" and [item] = "HITS" I typically use design view to set up queries and I can only do one of the three changes in this view. Is there are way to write this in sql to do all thr...

Combining two excel sheets into one, where one sheet has multiple values?
I have two excel sheets Sheet 1, and Sheet 2 within one Excel document. Sheet 1 has columns Name of a Person, PersonIDnumber Sheet 2 has columns PersonIDnumber, Email address of that person Combining them into one sheet which has Name of Person, PersonIDNumber, Email address can be done using VLOOKUP(A1,Sheet2!A:B,2,FALSE) The issue is Sheet 2 has multiple rows for the same PersonIDnumber meaning PersonIDnumber Email address of person 324 someone@yahoo.com 324 some_one@gmail.com 324 someone_76@hotmail.com Is there a way I can combine S...

Help Adding up multiple calculated cells
I have 8 columns that MAY contain NOTHING or may contain text chosen from a lookup name from a data validation pick list. Lic3 won't contain a value UNLESS Lic1 AND Lic2 are populated. I have 8 columns range named Lic1, Lic2, etc to hold a chosen license name. I have 8 other columns used for number counts relating to the lookups I have range named Lic1Cnt, Lic2Cnt, etc which hold a number such as 12. I need to add up the value calculated for each Lic based on looking up the text in a table, getting the price and then multiplying by the Lic? Cnt number. Some records may have only a singl...