Data Table With Subtotals
Jun 25, 2009
I have a table of data input, which I would like to be converted to a certain format. Please see the attached file.
The output is derived as follows:
Field2 Sum of amount1 Sum of amount2
Left(Field1,2) under the above Field2 Sum of amount1 Sum of amount2
Field1 (whose left,2 is the same as above) Sum of amount1 Sum of amount2
for each of the Field2 Field1 combination.
So is it kinda like pivot table with each row field having subtotals, except that the row fields are on the same column, and the subtotal is place on top.
View 9 Replies
ADVERTISEMENT
Apr 26, 2012
I am trying to add a subtotal to the RIGHT of my table for every change in DATE. The desired result looks like this:
Number
Vendor
Run Date
Amount
Subtotals
12580A
4/12/2012 233,220.14
[Code]...
Right now I am manually typing in a sum formula at each change of the value. Is there some way to automatically perform this calculation and have it be in a column of the table?
View 7 Replies
View Related
Jun 21, 2007
I have a seemingly very simple question but even though I've worked a lot with pivots I can't find the answer.
clientcode Amount countries
a1 1.000,00 kenia
a2 2.000,00 kenia
b3 1.000,00 kenia
b4 3.000,00 kenia
b5 2.000,00 kenia
c5 1.000,00 senegal
c6 3.500,00 senegal
c7 4.000,00 senegal
c8 5.000,00 senegal
Lets say I have a list like this and I want to count the number of clients (3) or countries(2).
I can only get the total of rows per client but not the subtotal 3 for the number of clients.
a - 2
b 3
c - 4
View 9 Replies
View Related
Jul 24, 2007
I can't find a way to display "just" subtotals which are > 500k ..
View 9 Replies
View Related
Aug 27, 2007
With the following data Excel's pivottable's subtotal shows underlying MAX total instead of subtotals relating to values displayed.
SalesRepOrderNoItemValue
ACON12334
ACON12322
ACON12412
ACON12424
ACON1248
Pivottable:
Sum of ItemValue
SalesRepOrderNoTotal
ACON12356
AC ON12444
AC Max34
Rather than use SQL (I don't have data on SQL server) or re-organising data by order number, is there a fix using calc items, calc fields or grouping to show correct MAX figures? Why Excel doesn't simply evaluate visible cells seems crazy to me!
View 9 Replies
View Related
Dec 21, 2009
I've just started using Pivot Tables and have a problem right away. Couldn't find anything with the search function, but am not really sure, what to search for, either
Part of my data looks like this
NameLot #BinAmountABC123CB18C174.025 ABC123CB18C-24.975 ABC123CB18C-24.975 ABC123CB18C12.616 ABC123CB18C-12.616 ABC123CB18C-24.975 ABC123CB18C-24.100 ABC123CB18C-25.000 ABC123CB18C-25.000 ABC123CB18C-25.000
Now this is a material that is stored on a pallet with the code CB18C. The total sum of material that is added to/taken from this pallet adds up to Zero. My Pivot Table, however, believes the total is something like 2.84217E-14.
Sum of Amount NameLot #BinTotalABC123CB18C2.84217E-14 123 Total2.84217E-14ABC Total 2.84217E-14Grand Total 2.84217E-14
This problem occurs several times, the raw data adds up to Zero, but the Pivot Table calculates these tiny, tiny totals. Is this a known bug or can Pivots not handle 3 decimal data?
View 11 Replies
View Related
Jan 18, 2007
When I have two or more fields on the left of the pivot layout, sometimes I only want the line item data and the grand totals, but not the various subtotals.
Is there a way to suppress the various subtotals created by a pivot table?
View 9 Replies
View Related
Jun 6, 2007
Pivot tables. They have a habit of adding automatic subtotals to each field of data you drop into the pivot. Is there any way of setting the default to "none" rather than "automatic". It's driving me insane.
I am toying with the idea of writing some code to eradicate this issue, but if there's something native to Excel which will do the trick, that would be much more efficient.
View 7 Replies
View Related
Oct 13, 2009
attached Pivot Table. There is a section, which I've marked, that shows how I want my Pivot Table report to look. I've had Excel 2007 kick me out a number of times when I've tried to filter and shift rows and columns to accomplish this and areas are frequently grayed out. Can someone please assist?
I'm attaching a file rather than going into a lot of detail here because I think a visual is easier here. The raw data, my attempt at the Pivot Table and my desired outcome all show on the same tab.
View 5 Replies
View Related
Jul 18, 2007
Does anyone know a setting to display pivot tables as a % of sub total automatically within the Pivot Table Settings? I have recreated a formula on the cells H:K of what I am looking for. see Attached.
View 2 Replies
View Related
Mar 17, 2014
I have an issue with a calculated item in my pivot table, because in the totals and subtotal it shows the sum of the column but I want to show the formula that I've specified to the calculated item.
For example, in the attached file, I have in rows the field "name" that has four values (A,B,C,D), in columns the field "Groups" that has "G1" and "G2". I add in the rows the calculated item "G1/G2" that has the formula G1/G2. The problem is that in the total the column "G1/G2" doesn't show the division of the total of "G1" and "G2".
View 3 Replies
View Related
Oct 29, 2012
I have two pivot tables where if one expands a subtotal (in this case 'manufacturer') to give the lines below (products made by the manufacturer), is it possible for this to be mirrored in the second pivot? I noted a previous comment on using slicers across two pivots but I cannot do this in this instance, although I am using Excel 2010.
View 1 Replies
View Related
Jun 30, 2006
I have a pivot table with 10 sub total rows, I would like to know if it is possibe to change the font into bold for rows who are subtotals.
View 6 Replies
View Related
Apr 24, 2014
I have sorted my data by three layers. First by Budget Center, then Invoice, and then Account. I am having trouble writing a formula that will total the amounts by account with respect to its invoice and budget center.
excel forum2.xlsx
View 4 Replies
View Related
Jun 10, 2008
I have a worksheet that uses Excel 2003's automatic subtotalling feature witSelectihin one of my macros. It works fine on my sample data, but when I try to run it on the larger set of customer data it hangs.
Any suggestions? I've been up all night, maybe this is just something really dumb, but I don't know. I stepped thru the macros, and it is hanging when I press f8 for the line that inserts the subtotals.
According to the task manager it really is hung, not just slow. Waited over 2 hours.
The line of code where it is hanging is:
Selection.subtotal groupby:=3, function:=xlsum, totallist:=array(14, 15, 16, 17, 18, 19, 20, 21, 22), Replace:=true, Pagebreaks:=false, Summarybelowdata:=true
I am a total newbie, so if there is a painfully obvious reason for why this is happening, don't think I'll be upset if you point out how dumb I am. Speaking of dumb, if this issue is posted elsewhere I apologize, I searched for hours before asking, but I may have looked in the wrong place.
View 9 Replies
View Related
Sep 23, 2009
I have a worksheet that I'm trying to rank program selling. The range has subtotals that I need to exclude, so I'm trying to rank over multiple ranges. I've thought about LARGE in an array function, but can't seem to make that work. Is SUMPRODUCT a possibility? I've attached the worksheet, trying to get the top programs (again excluding the subtotals in bold).
View 4 Replies
View Related
Jul 29, 2014
I am trying to create a Dashboard in excel (2010) using tables/pivot tables to build it. The data I am bringing into excel has these key fields of data: cost center+cost center description, general ledger account+general ledger account description, and YTD amount.
My problem is the data is from an external source report and the report has subtotals built in at cost center, and the report's format of subtotaling puts the cost center first and then the general ledger accounts below. There is no
formula value in the cell that has the subtotaled amount and the number of general ledger accounts can vary depending on whether there has been general ledger activity.
I want to take this format:
July YTD
Cost Ctr 1050 XYZ$6.00
625110 Supplies$2.00
650150 Postage$2.00
650550 Fees$2.00
Cost Ctr 1052 ZZZ$4.00
670500 Pens$2.00
and have it look like this
Cost CenterCost Center DescrGL AcctGL Acct descYTD Amt
1050 XYZ 625110 Supplies $2.00
1050 XYZ 650150 Postage $2.00
1050 XYZ 650550 Fees $2.00
1052 ZZZ 679200 Pens $2.00
Besides manually doing data moves and assigning a unique sort sequence number to keep the records together, how else can I quickly move my cost centers to a new column and keep the cost center with the gl account and $amount?
View 3 Replies
View Related
Jun 1, 2009
I have a table of data which represents data in different categories by week.
My ultimate goal is to have another table representing the data for each month - for instance - for each of the categories the data for :
06/04/2009
13/04/2009
20/04/2009
27/04/2009
is summed to make the data for the month of April.
The way I'm doing this at the moment is very long-winded
I'm using a whole new table - the size of the original weekly one - for each month. A calculation decides whether to effectively leave a cell blank or insert the appropriate data based on a date being within a particular range.
So in each "month table" there is the same list of week values:
06/04/2009
13/04/2009
20/04/2009
27/04/2009
04/05/2009
11/05/2009
etc.
but for each "month table" only the cells adjacent to the dates within the relevant month will return actual numerical values within them
This is an example of the forumula I am using in these tables:
=IF(AND(($AD7>=MIN($AE$5,$AE$6)),$AD7<=MAX($AE$5,$AE$6)),$C7,"NA()")
Then a master table sums the totals for each month.
I want to be able to keep this table but get rid of the ones for every month as the sheet is getting unwieldy!
I have tried several times to attach the sheet for clarity but each time upon trying to "Submit New Thread " I am getting page not found errors - the sheet is only 133KB and I have tried zipping and sending also - I can't make it any smaller.
View 14 Replies
View Related
Jan 5, 2009
I am having a problem with a formula I think I should be able to get correct but not sure if I am able to do so. On the attached file I have a filter on the TIER's for "Is grater than or equal to 5". What I need is a SUMIF formula that will take into account the filters. This formula needs to separate out between "GER", "IRE" and "UK" in cells C37, C38 and C39.
I have a subtotal in cell C35 which gives me the subtotal of all countries but I'd like to be able to have the subtotal separated out between the 3 countries and also still have the ability to manipulate the data so I could select different TIER's or a range of TIER's and Cells C37 - 39 automatically update themselves.
View 3 Replies
View Related
Sep 15, 2009
Have a look at the attache example. I have inventory items which have a fixed value. I have several quantity columns to reflect various inventory positions. (OnOrder, WIP, ATP, ATS, OnHand, PAB as at date()...)
At the top of the sheet I need to show sum of quantities and sum of values. In order to compute the values currently I need to hide columns to the extreme right to do the math on a row by row level and then sum the rows and copy the the value to another cell. In this case the cells with yellow background.
Is there a way to be able to write a formula that would return the sum of the qty multiplied by the value without adding an additional column. I would need to function like with filters as well (like =subtotal())
View 3 Replies
View Related
Aug 28, 2012
I am using subtotals to create groups. Sometimes there is only one row but more often there are two or more rows. I am trying to run a macro that needs to select either one row (copy into memory) of which there will always be a blank row below that, given the space where the subtotal does its calculations. I have tried a couple things i.e.
ActiveCell.Select
Selection.End (x1Down) . Select
Selection.End (x1Down) . Select
or
Range(Selection, Selection.End(x1Down).Select
ActiveCell.Rows ("1:8").EntireRow.Select
Is there a way to have the macro start and if it sees only one row followed by a space (blank row of subtotal that creates the needed break, that it will copy that one row into memory or if there are two or more rows that it will copy all those rows up to the next break and put that into memory? There can be anywhere from 1 row to several hundred rows.
View 1 Replies
View Related
Jun 13, 2006
have an excel spreadsheet linked to a network printer, it contains a list of what each user printed, how many pages, and the total cost. It is constantly updated. The total cost column contains the wrong price, so Im using this formula:
=If(C2=0,"",SUM(D2/C2)*(H2))
in EACH field on the total user cost column to extract the CORRECT price. So, I started my macro, highlighted the entire TOTAL COST column, then inserted my formula into each field in that column. The correct price displays for rows that contain data, and rows without data are blank. However, when I try to create a SUBTOTAL (Data --> Subtotals) for each user, I get the following error: "To prevent possible loss of data, Microsoft Excel cannot shift nonblank cells off the worksheet."
This is because I applied the formula to the ENTIRE column - even blank cells still contain the formula. How do I get fields without data to be completely blank?
View 6 Replies
View Related
Jul 12, 2006
I need the data "pulled down" into the subtotal row, so to get this after I subtotal, I'm sorting by C, and I've got some VBA deleting all rows where COLs A & B are blank (this is the longest part & the part I want changed the most - this gets rid of the non-subtotaled rows), extended replacing "Total" with "" in COL C and then inserting a lookup in A & B to get the data back next to the subtotals.
This takes really long and I'm sure there's a faster way to do this that I haven't thought of. All in all, I'm looking for something that will ONLY keep the subtotal rows, and will fill down the data to them while removing any non-subtotal rows.
View 9 Replies
View Related
May 27, 2007
Client Location Product Cost Sub- total
ABD Here Slurry $125.
ABE There Mud $525. $650.
Where I want to enter the cost and have Excel do the sub-total automatically.
Is there an easy way to do this, remember I am new to all of this? The spread sheet is already 147 entries long and will only grow and I don't want to have to figure out the sub-totals each time.
View 9 Replies
View Related
Aug 27, 2007
After using the subtotal function, I need to highlight and bold the subtotal rows. There are thousand over rows and it is impossible to do it manually, does anyone has a solution to this?
View 9 Replies
View Related
Dec 31, 2013
Formula/code to change the position of where the subtotals are placed. I don't want them appearing at the beginning or end of the data set but in a separate column beside each data set. how to access the code so I can try and alter it myself.
View 14 Replies
View Related
Dec 31, 2009
I have a column of numbers that after each sum there will be a subtotal. If the sum is a negative number then the new subtotal will be 0. Attached is a sample.
View 3 Replies
View Related
Dec 11, 2005
Let's say I have this set of data
A..................B..........C
date_______amt_ sub
12/1/05____ 1000
12/1/05____ 2000
12/1/05____ 5000 7000
12/3/05____ 2000
12/3/05____ 9000 11000
12/6/05____ 1000 1000
12/7/05____ 4000
12/7/05____ 2000 6000
So we see a subtotal according to the date, where the total values in chronological order are calculated to be
12/1/05 7000
12/3/05 11000
12/6/05 1000
12/7/05 6000
What sort of formula, then, do I put in column C that subtotals values in B according to the date in A?
View 10 Replies
View Related
Oct 21, 2007
it state "Use the subtotals command to sum the totals for each sales person) *Hint: convert the list to a normal range before calculating the subtotals.
I highlight Sales person and click Date but subtotals key is not showing up. i have attach the file its in the Subtotals worksheet.
View 14 Replies
View Related
Dec 12, 2008
Subtotal doesn't add cells hidden under a filter column but it does when grouping. How can I get groups to change a subtotal based on whether they are hidden or not. What I'm really trying to do is use conditional formatting to change the format when a group is expanded vs collapsed.
View 3 Replies
View Related
