Sumif function in Access

Please help!
I need to sum amounts if a condition applies.
The source table has 3 columns:
AcctNum, Amount, Category.
123         10          Tax
123         5            Ins
123         8            Loan
123         2            Loan
123         1            Tax
There are only 3 categories (tax, insurance, loan), but there can be 1 
account number with multiple categories.
I need to sum up the categories so the end result looks like:
AcctNum Tax Ins Loan
123         11  5   10

Thank you!
0
Utf
12/10/2009 8:31:01 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
1237 Views

Similar Articles

[PageSpeed] 16

Check out crosstab queries in Help.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Hurrikane4" wrote:

> Please help!
> I need to sum amounts if a condition applies.
> The source table has 3 columns:
> AcctNum, Amount, Category.
> 123         10          Tax
> 123         5            Ins
> 123         8            Loan
> 123         2            Loan
> 123         1            Tax
> There are only 3 categories (tax, insurance, loan), but there can be 1 
> account number with multiple categories.
> I need to sum up the categories so the end result looks like:
> AcctNum Tax Ins Loan
> 123         11  5   10
> 
> Thank you!
0
Utf
12/10/2009 8:54:02 PM
SORRY FOR THE DELAYED RESPONSE!

The crosstab query worked, thank you very much!



"Jerry Whittle" wrote:

> Check out crosstab queries in Help.
> -- 
> Jerry Whittle, Microsoft Access MVP 
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> 
> 
> "Hurrikane4" wrote:
> 
> > Please help!
> > I need to sum amounts if a condition applies.
> > The source table has 3 columns:
> > AcctNum, Amount, Category.
> > 123         10          Tax
> > 123         5            Ins
> > 123         8            Loan
> > 123         2            Loan
> > 123         1            Tax
> > There are only 3 categories (tax, insurance, loan), but there can be 1 
> > account number with multiple categories.
> > I need to sum up the categories so the end result looks like:
> > AcctNum Tax Ins Loan
> > 123         11  5   10
> > 
> > Thank you!
0
Utf
12/18/2009 7:05:01 PM
Reply:

Similar Artilces:

sumif formula #2
At the moment I am trying to work a sumif formula The problem I am encountering is once I have set the range and criteri it won't pick up the sum range as the range I am specifying is fo example J15:CB45 - it will pick up J15:CB15 but I need it to pick u the whole range - do you know of anyway I can resolve this. My formula is =sumif($J$3:$CB$3,E$3,($J15:$CB45)) but it doesn't see to work this one does though =sumif($J$3:$CB$3,E$3,($J15:$CB15)) bu that doesn't help me! I have put a print screen of the spreadsheet I am trying to work on. Thanks Jenni -- Message posted from htt...

Can't access hotmail from home
Hello, I run windows xp pro with sp2 and all of a sudden i was unable to access hotmail.com and my hotmail account linked to this cpu through outlook express. i get this message when i try to send an e mail. The server does not support the required HTTP methods. Account: 'Hotmail (1)', Server: 'http://services.msn.com/svcs/hotmail/httpmail.asp ', Protocol: HTTPMail, Server Response: 'Method Not Allowed', Port: 0, Secure(SSL): No, Error Number: 0x800CCC35 when i try to go to the web page hotmail.com it juts keeps asking me to log in and does nothing else... I ...

due date function
I try to make function for due payment. When I made copy file and then drag down the formula and it's succed. But for the cell that I not fill yet has due date also. How to make that cell became 0 (zero) if not fill the cell yet. example : A1 A2 19/8/05 =A1+30 - 30/01/00 - 30/01/00 Thanks Lando =IF(C5=""; "" ;C5+30) Explanation detail if ([logical test] c5 equals ""; then [result if true] ""; else [result if false] c5+30) Explanation brief if c5 eq...

sumif across multiple sheets in excel 2007 based on a condition
I have a small work book, tracks deliveries made and trucks used. I have seven sheets Saturday thru Friday and one sheet that totals everything up! The weekly sheets have 34 rows representing 34 stores that we service and the total sheet also has 34 rows that contain the totals for that week. Here is my question I am trying to, on the totals sheet, sum up the total trucks that we used per store per week. In the cell would be entered 0, 0.5, 1, 2 and so on depending on how many trucks we used for that day. Yes we use 0.5 if the store was combo'ed with another store. I ne...

Strange CObArray access violation
I have a CObArray which I've been using for some time within a class (derived from CDocument). I've added a new member function which throws an access violation exception every time I try to access the array. The exception is thrown with the following line: int arraySize = m_AtomArray.GetSize(); The access violation shows up here: _AFXCOLL_INLINE int CObArray::GetSize() const { return m_nSize; } I have initialized the array within the Class initialization list, and successfully traced the size of that array within the class constructor. As I have already mentioned, other member...

Function Parameter Type
Hi I'm having problem calling a method of a COM. The signature of the exposed method is like this. HRESULT GetMailLists(VARIANT* bstrLists) If I call this method from a C# code, the signature changes to void GetMailLists(out object bstrLists) In C#, I called it as object obList; users.GetMailingLists(out obList); and obList type changed to "System.Array" and was filled with an array of desired string values. Now the problem is that I have to use this method from my VC++ code and have to use late binding for using the COM. Can someone tell me how would I call this method ...

accessing rows with macros
Hi, I'm quite new to excel. I'd like to loop through all the rows in my sheet and compare the text in the cells of the first column with some string variable priorly set. How can I do that ? Also I would like to disable my cells for editing, so that only my function run when I click on a button can modify those cells. I've done it through protecting the sheet from editing, but then even my macro can't edit the cell without having to enter the password;) Thx For i = 1 To Cells(Rows.Count,"A").End(xlUp).Row If Cells(i,"A") = "somevalue&qu...

Cannot Access CRM 1.2 from Web
Hello. Thanks in advance for any help!!!! I am having a heck of a time accessing CRM from the web. I set up a host header in IIS. "crm" I configured an alias in DNS. crm --> 192.168.3.100 Now, locally, I can get to CRM in the browser of the CRM server using the host header - so the header works. That's great. From the browser of another PC on the LAN... I can access the companyweb I can access exchange But I cannot get to crm. SO IIS is working. But it won't give me crm. I am trying the following. http://servername-crm-1/crm http://192.168.3.100/crm Neither wor...

Size of Excel file Access db exports to.
I have an Access db that exports about 350 queries to a specific Excel file. This is done on a weekly basis then monthly basis. The db and exporting works great. However the size of the file. The size of the file after the save is as large as my largest month of data which makes sense. Is there anyway to compact an Excel file like you can in Access to get reclaim space that may be used in the monthly data but not in the weekly data? The weekly data was making the file about 26 MB. After I ran the monthly data it ballooned to 36 MB. All but about 20 of the queries are created through code. Wou...

Access to RMS Database
Having issues connecting to RMS DB from backoffice computer...and not so co-incidentally I started getting some error messages on the Server regarding NetBT (Net Bios over TCP/IP) having issues (not starting due to driver init error, couldn't create driver...of course MS diagnosis is "a transient condition that can be safely ignored"...yeah right). Anyhow, I presume that RMS needs NetBt in order to facilitate SQL/DB access and transport ? (in addition to network file transfer and printing too from what it looks like...basically the server is isolated from the world save fo...

SUMIF or SUMIFS
Here is what I have Col A Col B Col C 1310 3 3,463.00 1315 3 740 1330 3 1369 3 -178 1375 3 -105 1640 3 110 135 4 1310 4 1,460.00 1315 4 1,521.00 1375 4 -65 1310 6 3,284.96 I am trying to figure out a way to add column C to a new cell if Column A is between 1310 and 1369. Any suggestions?? =if(and(A1>1310;A1<1369);C1;"") HTH "Jeff" wrote: > Here is what I have > > > Col A Col B Col C > 1310 3 3,463.00 > 1315 3 740 > 1330 3 > 1369 3 -178 > 1375 3 -105 > 1640 3 110 > 135 4 > 1310 4 1,460.00 >...

Password Change Functionality in Exchange 2003
We have an Exchange 2003 server (single server) running on W2K3 SP1 w/ OWA configured. Everything works well/ SSL is enabled. I followed the instructions described in KB Article 297121. The Change Password button is present and when clicked, a pop-up appears: "Internet Service Manager for Internet Information Server 6.0" Five fields are listed: Domain, Account, old Password, New Password, Confirm New Password....after filling in the fields and clicking on OK, I get "Error number: 5" Even if I leave all the fields blank, same error. I did notice when I logged on dire...

How do I throw in an ISERROR function on a complicated VVLOOKUP?
Hi, I have a working VLOOKUP below. Of course the desitination cell says #N/A until I put something in A6. I tried ISERROR but it says too many arguments. Did I do this wrong? Can anyone help? Thanks. =IF(VLOOKUP(A6,CSM,8)="X","X",IF(VLOOKUP(A6,NPM,8)="X","X",IF(VLOOKUP(A6,PM,8)="X","X"," "))) How about =if(a6="", "",formula or a6=0 -- Don Guillett SalesAid Software donaldb@281.com "KenRamoska" <KenRamoska@discussions.microsoft.com> wrote in message news:C4469806-8831-4631-B74D-687...

SUMIF or SUMIFS help
I have 2 sheets in one workbook (Sheet 1 and Sheet 2) Sheet 2 has 3 columns: A B C MAKE TYPE QTY 1 toyota compact 10 2 ford pickup 15 3 toyota sedan 20 4 toyota pickup 80 5 nissan hybrid 10 Sheet 1 has 2 columns: A B MAKE PICKUPS 1 toyota ? (SUM) I need Sheet 1,B1 to calculate the total number of matching items in sheet 2 that matches the data entered in Sheet 1,A1. In other words, I need sheet 1,B1 to automatically sum up the total number of to...

Copy Functionality Lost
Up until a day or two ago, I could select a cell to copy, paste it into another cell and the original copied cell would remain available to be copied again (it would be highlighted with the dotted border). Now, after one paste the copy cell reverts to normal, and I have to re-select it to copy into another location. I know I can use CTRL or SHIFT, but I find the retention of the copy ability useful. This is a networked office PC and imagine I might have to ask our IT Dept (as Administrator) to do something. Any ideas appreciated, thanks If you paste by pressing Enter you'll exit ...

another sumifs plea
I'm struggling to convert a sumifs line from 2007 to excel 2003. The line I have working correctly in 2007 is: =SUMIFS('Washing MC Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"= 0.00",'Washing MC Rental'!$L$4:$L$21,">28/2/2010")-SUMIFS('Washing MC Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"= 0.00",'Washing MC Rental'!$L$4:$L$21,">31/3/2010") Can any one please help me to put this into 2003? Best Regards Dave =SUMPRODUCT('Washing MC Rental'!$K$4:$K$21,...

Function is killing performance
Hi - I'm using a custom function in a query and it takes forever - was hoping someone might have an idea of how to speed things up a bit. A little more info: the function is relatively simple (4 arguments that are run through an IF statement to pick a value from another table (only 12 records). The query is run against a table with 41k records. The numbers don't seem to big here, and the query takes over an hour. Any ideas?? Where & how are you using the IF's? Sounds like a coding problem to me Pieter <stephen.h.dow@gmail.com> wrote in message news:1189019241.93425...

Wish list for Entourage Web Access fixes
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Email Client: Exchange I was fortunate enough to work with Entourage Web Services since the public beta, but now that a final version is out in the wild, we are now noticing issues that really need to be taken care of. <br><br>To get compatibility issues out of the way, all of these reports involve EWS version 13.0.3 connecting to Exchange 2007 running the latest service pack. <br><br>1. While category synching is finally supported in calendars, it is not supported if the calendar is being ac...

need to run another access program from a front-end
I have a front end that has exceeded its no. of objects (2000) as i cannot make mde out of it. What i plan to do is rmove some functions from the file and put it into another mde file which would be called fron the first front-end. How can I do this ? yusuf "programsimpleeh?" <programsimpleeh?@discussions.microsoft.com> wrote in message news:D1A510E6-C0D0-41EF-AB61-A81F467C634A@microsoft.com... >I have a front end that has exceeded its no. of objects (2000) as i cannot > make mde out of it. What i plan to do is rmove some functions from the > file &...

FormulaArray inside function
Function test(drybulbtemp As Double, percentrelativehumidity As Double) Selection.FormulaArray = "=INDEX(C[-4],MATCH(drybulbtemp percentrelativehumidity,Dry_Bulb_Temp&Percent_Relating_Humidity,0))" test = Selection.FormulaArray End Function what i am trying to do is create a function where you say the cell is: =test(1,2) and then it will run what I have above plugging in the values 1 and into the function I have above and it will then output the value in th same cell, but this isnt working, I know its not the way to do it, bu i am new to vba and need this to work. Cur...

sumif vs sumproduct question
Hi, I have a simple spreadsheet with column B containing all dates, and column D containing all numerical values. The dates range throughout a year. At the bottom of the sheet I've entered January through December in another column. To the immediate right of the month named, I have a formula which gives me the total for that month. For instance, next to January my formula reads: =sumproduct(--(month(b6:b370)=1),d6:d370) this formula works, but it seems to me I should be able to use SUMIF too. I've tried =sumif(d6:d370,month(b6:b370)=1,d6:d370) but that just returns a blank ce...

Update Excel Database via Access Main Database with a script
Hello, i am wondering about something fairly complex, i have two databases, one in the access format (the most utilized one) and another one in excel format, Since we use the Access one the most, but cannot print the informations that it contains, i was forced to create a new one which you can print off of, so here is the issue, whenever we add new information to the access database, we need to manually update the excel one and it's very unconvenient, because since we do not use it very often, it falls in the depths of The Documents. My question to any expert out there is, Is there ...

How to get add-in functions to show up in function list?
I managed to create an add-in (test.xlam) and get in installed. It has one function: Function myarea(length As Variant, width As Variant) myarea = length * width End Function If I enter "=myarea(3,4)" in a cell, I get 12. What I don't get is prompted with the name of the function. If I type "=m", I am offered choices from Match to Multinomial, but not MyArea. Can I get my add-in functions listed and then, once selected, promted for the arguments? Which version of Excel are you using? In Excel 2007 you get user defined functiona listed as well as the bui...

Help with Function: Between Time
Hi, Why this function is not working? If a time follows between 12AM and 3PM it should show Day shift, anything else Night Shift If Time > #12:01:00 AM# < #3:00:00 PM# Then MsgBox "DAY SHIFT" Else MsgBox "NIGTH SHIFT" End If If Time > #12:01:00 AM# AND Time < #3:00:00 PM# Then MsgBox "DAY SHIFT" Else MsgBox "NIGTH SHIFT" End If Note that you need to repeat the entire comparison. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Bre-...

Microsoft excel and ms access
I have 40 forms that I need to fill in and generate reports. I don't want to use access for this purpose, neither I want to use PHP programming. I am wondering whether or not it is possible to use excel as an entry form and access database as a data recording unit. Plz. Suggest Regards Sam. Heres a couple of links that may help: Better Solutions - http://www.bettersolutions.com/excel/EHX116/LT423111411.htm Bristol Uni - http://www.bristol.ac.uk/is/learning/documentation/excelxp-t5/excelxp-t5.doc Also try doing a Goole search "sAMBEDAn kOIRALa" wrote: > I have 40 for...