# subtotals

1. ### DAX Subtotals Profit and Loss statement

I'm using the following method from Imke to produce a nicely formatted pivot table in Excel: https://www.thebiccountant.com/2016/07/21/easy-profit-loss-account-scheme-reports-power-bi-power-pivot-dax/ However, I am building on this to produce projected numbers. For each reporting heading the...
2. ### Pivot Table Calculated Field subtotals

I am working on a pivot table based on data that has a region, a sub-region, dates, and then the available work potential in each sub-region by date. I need to do two things, 1. I need to create a calculated field based on the available work potential 2. I need to change the subtotals to...
3. ### VBA inserting subtotal(s) rows

Before I totally kill my day trying to do this, I thought I'd post this considering how quickly perfect solutions are created. I have a sheet which is already sorted by column H (starting in row 5). [thanks, Fluff] I now need to put in a subtotal row for each group of names in H5:Hn and total...
4. ### Loop through spreadsheet and replace static subtotals with formula

Hopefully this make sense. I have this table I would like to loop through and change the subtotals to actual formulas. https://imgur.com/YC0m2LP My Thought was to loop through the A column because the subtotals are placed the row after the name. I thought if I could calculate the range...
5. ### Rank - exclude subtotals

How do I setup a rank, type formula, to exclude subtotals? I am trying to pull a top 10 list. if I do a subtotal on the original data, I lose most of my top 10 values.
6. ### Rounding Formula Parameters to Make Summations Accurate

Hello, I have reports that are in whole dollars and decimals with subtotals and totals. I need to report these in thousands. The round(x,-3) formula works for presentation for the individual rows and totals, but the sum does not equal the rounded totals and subtotals. I end up having to add...
7. ### Sorting grouped items

Hey, I have grouped amounts and it equates to the subtotal. I would like to sort all of the subtotals will my groupings follow?
8. ### Is there a way to replace all formulas except subtotals?

Is there a way to replace all formulas except subtotals? Currently I'm doing it manually...
9. ### Removing Subtotals with a value of Zero and related data

I have a macro to remove all subtotals that equal zero along with the data making up the total; however it appears to be deleting more data than it should. For example, I know the outstanding total should be \$157,542 but when I run the macro to remove the zero subtotals my outstanding balance...
10. ### Calculate percentage of selected range/category from a large set of data

Hi all, I am new to mrExcel. I have a typical requirement. Currently I have one document which as the inventory of the vehicles. I need to calculate the percentage of the number of vehicles by city in a new column. The sample data as follows <tbody> Country State City Brand no of cars USA...
11. ### VBA or formula - Subtotal of Non-Hidden columns

Hi, Have a report, totals represented in cells b3:ac3. I need to hide columns at times and would like the subtotals. I tried this thread but can't get it to work. Can anyone help? https://www.mrexcel.com/forum/excel-questions/655374-subtotal-column-hiding.html
12. ### PowerPivot table Multiple subtotals

Hi, I'm wondering is it possible to have a power pivot report table that has the details of units sold by one criteria. Then also shows multiple subtotals at the end. IE: Total units sold by Brand is the report. Then I have a subtotals at the bottom listing the season. Another subtotals listing...
13. ### Pivot Table Subtotal,

Pivot Table Subtotal, How do I change the subtotal column, my pivot table has approvers on column A, vendors on column B and the result of Sumiffs on column C, I want the subtotal by column A, but the pivot table subtotals by column B, that is just repeating on what is in column C. It is...
14. ### Automatically insert subtotals help

I have a pivot table with multiple subtotals. Off to the right, we added manual adjustment columns not part of the pivot table. Is there a way to automatically insert subtotals (highlighted in yellow bellow) for the manually added columns? I have thousands of rows of data so manually creating...
15. ### Pivot table subtotals

Hello, I got rid of subtotals in this pivot table, but how can I get those hierarchy titles and empty spaces to go away?
16. ### Dynamic Range with SUM Function Calculation

For my macro the user puts in a year and month to bring up monthly expense table. I have a subtotals and month totals rows after the last date of the month. I can't figure out how to sum the subtotals for each column in my subtotals row because the number of rows above it could change because...
17. ### Pivot Table Subtotals?

Hi all I use pivot table subtotals and there are not normally a problem. However I have a subtotal that when set to SUM is correct, but when set to AVERAGE, MAX or anything else = 1. Which is obviously not the correct value. Anyone know why?
18. ### Subtotals in Pivot tables

I'm building a pivot table with quite a few columns. By default excel puts a column subtotal and doesn't repeat the row labels. I can remove the subtotals and make the labels repeat from the field menu. I wondered if there was somewhere no subtotals and repeat labels could be set as the default?
19. ### Subtotals

Hello, I am trying to figure out if there's a way to use VBA to find subtotals based on two criteria. The data is already sorted by these two criteria (line number and department number) and I'd like to be able to draw a bottom border across the cells and put the subtotal on the last cell that...
20. ### Subtotal filtered page items

Anyone have any idea what this is and how to use it? I've tried having filtered items in a pivot table with subtotals on and it's still grayed out (xlsx file).