(Sub)totals

Hello everybody.
I have the following problem:
I have an Excel file with Column B 'Quantity' and column F 'Sector'. Now i
need to count all the quantities within the same sector
Sorting + AutoSum isn't an option, since the file has other calculations in
it as well, that also depend on a sort.
There's in my Dutch version a function 'DBSUM' and a function
'TURNTABEL.FETCH' ... these are translated terms, so I don't know if they
match with English versions ... but could someone please tell me how these
functions work, since i can't get them right (not by typing everyting
manually, nor by using Excels 'function input window'.
So, if you could help me out on one of these 2 functions, OR offer me an
alternative, i'll be close to eternally gratefull ;) 


0
kwakkel (12)
3/17/2005 9:10:27 AM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
347 Views

Similar Articles

[PageSpeed] 44

Hi

see my response under microsoft.public.excel.misc
you also might like to check out pivot tables
see
www.contextures.com/tiptech.html
for details

Cheers
JulieD

"Kwakkel" <kwakkel@skynet.be> wrote in message 
news:42394988$0$28073$ba620e4c@news.skynet.be...
> Hello everybody.
> I have the following problem:
> I have an Excel file with Column B 'Quantity' and column F 'Sector'. Now i
> need to count all the quantities within the same sector
> Sorting + AutoSum isn't an option, since the file has other calculations 
> in
> it as well, that also depend on a sort.
> There's in my Dutch version a function 'DBSUM' and a function
> 'TURNTABEL.FETCH' ... these are translated terms, so I don't know if they
> match with English versions ... but could someone please tell me how these
> functions work, since i can't get them right (not by typing everyting
> manually, nor by using Excels 'function input window'.
> So, if you could help me out on one of these 2 functions, OR offer me an
> alternative, i'll be close to eternally gratefull ;)
> 


0
JulieD1 (2295)
3/17/2005 9:35:01 AM
The SUMIFfunction works like a charm. Thanks a lot.
I think those pivot tables are what i called 'turntabels' ... so i'll check 
out  that site as well.
Thanks again!


"JulieD" <JulieD@hctsReMoVeThIs.net.au> schreef in bericht 
news:%23wGEJTtKFHA.1096@tk2msftngp13.phx.gbl...
> Hi
>
> see my response under microsoft.public.excel.misc
> you also might like to check out pivot tables
> see
> www.contextures.com/tiptech.html
> for details
>
> Cheers
> JulieD
>
> "Kwakkel" <kwakkel@skynet.be> wrote in message 
> news:42394988$0$28073$ba620e4c@news.skynet.be...
>> Hello everybody.
>> I have the following problem:
>> I have an Excel file with Column B 'Quantity' and column F 'Sector'. Now 
>> i
>> need to count all the quantities within the same sector
>> Sorting + AutoSum isn't an option, since the file has other calculations 
>> in
>> it as well, that also depend on a sort.
>> There's in my Dutch version a function 'DBSUM' and a function
>> 'TURNTABEL.FETCH' ... these are translated terms, so I don't know if they
>> match with English versions ... but could someone please tell me how 
>> these
>> functions work, since i can't get them right (not by typing everyting
>> manually, nor by using Excels 'function input window'.
>> So, if you could help me out on one of these 2 functions, OR offer me an
>> alternative, i'll be close to eternally gratefull ;)
>>
>
> 


0
kwakkel (12)
3/17/2005 10:01:37 AM
I now have the following (similar) problem:

I still have columns B and F, but also a column S 'Yas Asw Spread'.

Now i have to make a weighted average from S per sector F.

So:

If F2 = 10002, I'd multiply S2 with B2 and divide by the total of column B.

I thought i'd add the SUMPRODUCT around your SUMIF, but unfortunatly that 
doesn't work.

Is there any other way i can solve this, preferably without pivot tables, 
since i have to admit, those don't make much sense to me :)

Thanks in advance (and keeping my fingers crossed ;) )

"JulieD" <JulieD@hctsReMoVeThIs.net.au> schreef in bericht 
news:%23wGEJTtKFHA.1096@tk2msftngp13.phx.gbl...
> Hi
>
> see my response under microsoft.public.excel.misc
> you also might like to check out pivot tables
> see
> www.contextures.com/tiptech.html
> for details
>
> Cheers
> JulieD
>
> "Kwakkel" <kwakkel@skynet.be> wrote in message 
> news:42394988$0$28073$ba620e4c@news.skynet.be...
>> Hello everybody.
>> I have the following problem:
>> I have an Excel file with Column B 'Quantity' and column F 'Sector'. Now 
>> i
>> need to count all the quantities within the same sector
>> Sorting + AutoSum isn't an option, since the file has other calculations 
>> in
>> it as well, that also depend on a sort.
>> There's in my Dutch version a function 'DBSUM' and a function
>> 'TURNTABEL.FETCH' ... these are translated terms, so I don't know if they
>> match with English versions ... but could someone please tell me how 
>> these
>> functions work, since i can't get them right (not by typing everyting
>> manually, nor by using Excels 'function input window'.
>> So, if you could help me out on one of these 2 functions, OR offer me an
>> alternative, i'll be close to eternally gratefull ;)
>>
>
> 


0
kwakkel (12)
3/17/2005 11:55:42 AM
Reply:

Similar Artilces:

Make a sub form visible with a combo box selection
I have a form (frmBids) that has a combo box (cboContractType) that pulls two values from table (domContractType): Column 1 - ID; Column 2 - Name. The bound column is Column 1 (hidden in the combo box) and it stores the numeric values into tblBids!ContractType. I am using the combo box selection to make visible a sub-form (sbfBidSubs) located on a tabbed page (tabBids) further down the form. I set the visible property on the sub-form to be No by default. I then set the AfterUpdate properties to make visible the sub-form when the user selects option 2 (Sub) in the combo box, but not when se...

sum total
Hello, I have al total in column a ($3.95) and a total in column b 4,803. When I try to divide it the total comes out as ($18,987.28) but it should be ($18,971.85). What am I doing wrong. Thank you for any help you can give me. -- thank you mac See http://www.mcgimpsey.com/excel/pennyoff.html My guess is that your "3.95" is actually more like 3.9532 In article <2909D013-4352-4235-A904-29D66826CCFA@microsoft.com>, mac <mac@discussions.microsoft.com> wrote: > Hello, > I have al total in column a ($3.95) and a total in column b 4,803. When I > ...

Totalling unique values
I have a list with the following Cust Qty A 1 B 2 A 2 C 1 How can I subtotal Quantities for each customer? (i.e. Cust A should subtotal 3) I have already used the Advanced Filter to pull out the unique customer ID's I just don't know how to get the subtotal for each. Hi Stumped, Fist thing is to sort the list by customer number. Then go to data>sub totals> at each change in Cust>sum>Qty. You will get a sub total and a grand total. HTH ray "Stumped" wrote: > I have a list with the following >...

Sum total bolded figures in a column
I am trying to come up with a simple cell formula to total or sum amounts bolded in a column. Any Ideas? Thanks from a person needing help. mikeburg -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24581 View this thread: http://www.excelforum.com/showthread.php?threadid=392229 There's nothing built into excel that will sum values based on formatting. You could use a User defined function, though. Option Explicit Function SumBold(rng As Range) Applicati...

How can I stop a calculation when a total reaches a maximum value?
I would like to be able to stop a calculation when my total dollar value in a certain cell reaches $7,000.00. Right now, I have it set up to calculate 1.8% of a gross dollar amount. When the gross dollar amount reaches $7,000.00, I would like Excel to stop calculating the 1.8%. Is there a way to tell Excel to do this? Hi! It would be helpful to know how you have set it up so far and how yo make it do its job. Al -- Message posted from http://www.ExcelForum.com PCC =MIN(7000,A1*.018) Gord Dibben Excel MVP On Thu, 29 Apr 2004 15:46:01 -0700, PCC <anonymous@discussions.microsoft.c...

Sub list and rum macros in the function
1- I want make a sub list in the excel. I make the dop down list in the cell A1 contain: Eua, Brazil, Ingland, Canada. And i want that in the cell B2 apeard: Texas, Arizona, Florida when in the cell A1 is Eua. How I do it? 2- I would like execute a macro into If funtion. I make a macro that delete a list and I want that in the cell A1 when I select a option in the drop down list using the If funtion..... If the cell A1 = deleteA, execute the macro4 that erase the list A. How I do it? -- Tiago Gonçalves Brandão "T-shugo" wrote: > 1- I want make a sub list in th...

Calculating Total from selections
Hello everyone, Can anyone help / make suggestions in achieving the following: I am trying to create an invoice that summarises the total of the products at the bottom of the spreadsheet. What I want to achieve is some way of having various products in a row which a user can select via some sort of check box (giving the product a boolean value). Each product would have a price value attached to it and when it is selected the price should be added to the total. This would therefore allow the user to select various products and the total would be calculated accordingly. This seems relativel...

Opening a sub-form from a command button
I have a sub-form with in a main form. The sub-form opens with the form. I want tthe sub-form to open separtely, such as with a command botton. And if this cannot happen, then I want to delete the sub-form and have the form (that was the sub-form) to open with a command button, but for it to be sized to fit with in the area that the sub-form had opened in. I acually have several sub-forms that open with the main form, each with in their own page. The the database on the server and with multible users, the database slows way down. I want to open each sub-form as needed (ins...

how can I get the total hrs of a delay when it is greater than 24
I work for an airline and sometimes I need to know the total hours of a delay, and when the delay is greater than 24 hours. In my report I hav something like this: arrival time in colum A departure time colum B result in colum C 10/23/09 21:00 10/24/09 23:00 2:00 and really the delay was of 26 hours. How can I obtain this? I already set up my Tools-Options-Canculation to 1904 date system Format Custom as [h]:mm No need to use 1904 date system -- Kind regards, Niek Otten Microsoft MVP - Excel "El Ixmahana" <ElIxmahana...

X & Z Totals
Is there a way to sort the Department Totals on a X & Z report? We were informed that the sort is based on the order the departments were created and cannot be changed. -- Many thanks Jack Master ...

Code help needed for Option Buttons to control Subs
I have a User form with (20) option buttons on it. It does what I want by only being able to check 1 option button. Option Button Names: OB_601 OB_602 - OB_619 OB_620 What I need help with is when user checks the correct option button, that button exicutes code located in a module. Code in Module: There are (20) Subs named like below Sub String_01() Sub String_02() - Sub String_19() Sub String_20() I think the code for each Option button is something like this, because there default position is False and they only show true when selected. If OB_601 = True the...

Total Taxable Amount
How do I calculate Total Taxable Amount for Employee Expese Entry form in Project Accounting module? What settings are used for determining this amount? On Aug 3, 12:09=A0am, Nik <N...@discussions.microsoft.com> wrote: > How do I calculate Total Taxable Amount for Employee Expese Entry form in > Project Accounting module? What settings are used for determining this am= ount? Dear Nik, There are two ways you can calculate the tax amount. Manual:- Use the More Info button in the Employee Expense Entry window form and select the tax schedule ID. Automate:- Th...

Can I create sub-functions in a cross-functional flowchart?
Trying to create a template for my organization to map processes to. Goal is to map the organization to the 'function bars' in Visio's cross-function flowcharts. However, I would like to create some 'sub-functions' for lower organizational elements (i.e. divisions under a department). Is this possible?? ...

Running Excel Pivot Table with Local cube on http:// Sub-Web???
Hello I have a SQL 2000 local cube that I created on my computer that is accessed by an Excel (XP) pivot table workbook. I would like to place & run both on a secure sub-web on my company http:// website. Both files were FTP'ed to the sub-web successfully. When opening the Excel file I am prompted for the local cube: the prompt is referencing the cube location on my computer where it was originally created with SQL 2000. I am unable to point it to the sub-web location. It appears that the Excel workbook has hard coded the original cube location back to my computer. Is there a way ...

Update table with a Running Total
Hi, I have a table [Table1] with the fields laid out like this... StationID Date Value CumulativeValue What I need to do through VBA is populate the YTD [CumulativeValue] for each [StationID] for each [Date]. The dates for each Station run from Apr 1 to Oct 1 and the [Value] field contains an Integer value for each day. So what I'm really wanting to do is populate the YTD [Value] for each [StationID] for each [Date]. Thanks, Ken Instead of storing that CumulativeValue in the Table, you can see that thru a Query Report where you havs "From - To" dates. HTH - Bob "KP...

Total of Time
I'm recording the time I spend working on a piece of work in a spreadsheet along with meeting details etc. I've 2 columns that are start and finish times and a third that works out duration. I then want to total the duration into total hours but I can't find a way to do this Any help please TIA Assuming your duration is in column C, just use =SUM(C1:C100) XL's default display will "roll over" every 24 hours. To get the actual total, choose Format/Cells/Number/Custom [h]:mm In article <#NJENCGYEHA.3596@tk2msftngp13.phx.gbl>, "A" <a...

Link two subforms in datasheet view (one sub-form, one sub-subform
I’d like to know if this is even possible. I have a form named “Frm Volunteer Opportunities” with a subform named “SubFrm Volunteer Opportunities”. The subform must remain in datasheet view and has fields like: [Event ID], [Event Date], [Start Time], [End Time], etc... I want to link another form (a sub-subform?) to the datasheet so that whenever my curser is located in one of the datasheet rows, this other form will present all volunteers scheduled for that event. Then, when you move the curser to the next row, those names would change. I’m guessing this means that the m...

split category and sub-category
Money 2005 How do I get it so that the category and sub-category are split (two seperate fields)? I had it setup that way, but after updating to 2005, it's back to a single field. The same thing happened last time I updated Money, but I forgot where the setting was (or it's moved or not there now). -- Mike Go back to M04. This option was eliminated in M05. "Mike Brearley" <mike_brearleyDONTDOIT@NOSPAMhotmail.com> wrote in message news:A_F8d.274950$bp1.127132@twister.nyroc.rr.com... > Money 2005 > > How do I get it so that the category and sub-catego...

Subtract a group of cells from a total based on ending date
I don't know if this has already been discussed or not but I couldn't find anything about it so here's my question: I am working on a spreadsheet that calculates interest owed based on how many months have been paid on a contract. The original spreadsheet was based on a formula that took the date of the last payment and subtracted it from the current date, then calculated the interest on that period of time. Well, that doesn't work if the contract is a 2 year contract from 2001 and is already over. We can't charge more interest than what they were originally quoted,...

Parse sub elements with namespace
How can I parse the component elements per partList in the following xml file? If I remove the namespace section of the xml my code works. I am not sure why the namespace section impacts my code? Of course when I remove the namespace I section I change the selectnode reference accordingly. <?xml version="1.0"?> <workOrderReleased xmlns="http://www.qad.com/qdoc" xmlns:enc="http://www.w3.org/2002/12/soap-encoding" xmlns:qcom="http://www.qad.com/qdoc/common" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi...

Total Cash Recieved vs. Total Invoiced
Hello, Currently we create the above listed report by access the IVC30101, IVC30102, and RM 00101 tables via MS Access to create. However I would like to create a report within Great Plains. I would need to have the total amount invoiced per customer per time period and then the total cash applied to those invoices. Is there a 'beginning' report that is standard I could customize to do this? Currently the report totals by month and by item number as well ...

Totaling Each Page
I have a monthly report that I need to submit. The report consists of different amount of rows each month, and I need to display each page total at the bottom. The original source data comes from a csv file, which usaully spans 500 - 900 rows. Is there an easier way to display page totals on each page, then using the autosum function each column using the last row on each page? Currently I merge the data source and then print preview the worksheet, which displays the page breaks. I then insert a new row at the bottom of each page to create the page totals. There has to be an easier way!...

Component / Sub-component
I want to model the concept "Component / Sub-component" with rectangles for example. If I have component A with it's sub-components A1 and A2, then I have 3 rectangles with A containing A1 and A2. Is it possible to constraints A1 and A2 moving out of rectangle A? Then the relation between them is lost. (In Flowcharter (used years ago) this was easily done). Thanks in advance... On Wed, 2 Apr 2008 03:55:31 -0700 (PDT), Qi <qixiangchi@gmail.com> wrote: >I want to model the concept "Component / Sub-component" with >rectangles for example. >If I have compon...

Cell format for total hours:minutes?
I formatted a cell as Custom >> hh:mm and put in 82:25. I get 10:25 displayed in the cell, and 1/3/1900 10:25:00 AM in the formula bar. How do I get it to display both places as 82 hours and 25 minutes? Also, in another cell, it's going to have to display as hours-decimal- tenths of hours. What do I put in that cell to pick up the value and display it correctly? Thank you. Ed Format cell custom as [hh]:mm without the brackets you will only get what's over after you divide it by 24 3x24 is 72 and 825:25 - 72:00 is 10:25 so you only need to change the format -- Regards...

Data Cell Formula
Hi, Wondering if someone can assist please... We have a table that has the following calculation in it: =SUM(B10*60+C10)/60*E7 Which is determining the cost of a phone call using minutes, seconds and rate per second. What I need to be able to do is say that the above calculation is correct, unless the total is less than x amount (ie, .37), then that total should equal 37. This is the current train of thought, which is obviously wrong.... =SUM(B10*60+C10)/60*E7+IF(B10=0,,+E6)+IF(B10=0,,+E5)+IF(B10<=14,,(B10-15)*E4)+IF(B10>=10,+E3,) TIA Submitted via EggHeadCafe - ...