# subtotal

1. ### how to macro to auto sub-total and grand total of manpower plan used in the project

I have "manpower plan" sheet, and the No. lists in Column "A from 1 to N (example is from 1 to 3), Month in row 2 from 1 to M (example is from “E” to “P”). Each time, I need to calculate the subtotal Manpower for each of Team (from 1 to N) including grant total for all teams by each month from 1...
2. ### Pivot Table Subtotal setup

Hello, Please see attached image for reference. I have a pivot table that shows the data broken out by Quarters in the columns and Year in the rows. When trying to add subtotals for Division (BAL, KC) the subtotal combines both 2021 and 2022 values instead of splitting them. Basically I was...
3. ### Issues subtotaling and breaking down the subtotal outline to obtain records for copying.

I download each month a report that provides a list of telephone numbers that I keep track of to show how many incoming calls were made to one of our call centers and the duration of those calls. What I am doing is placing the downloaded data into the worksheet "Original Data". From There I...
4. ### Replace Subtotal to Sum Formula with VBA Code

Hi everyone, Is there an excel VBA code that allow you to replace part of a formula after cells selection ? For example, after selecting multiple cells, select the code to replace SUBTOTAL(9, with SUM( Some formulas examples: =SUBTOTAL(9,EA10:EA12) change to =SUM(EA10:EA12)...
5. ### Sumifs and subtotal (or not)?

I have a table with 3 columns: transaction date, costs/revenues, tax/deductable tax. Each row is one revenue or cost and the dates goes on multi-year seamlessly (i.e., I have a table that has this from 2019). What I'd like is to give a date in a cell (say A1), and have a formula that outputs a...
6. ### Excel Formula: incorporate MATCH with SUBTOTAL to count number of rows

Instead of =COUNTA(A11:A5000) below formula automatically adjusts to however many rows with data, instead of limiting to 5000 rows, or putting a million rows: =MATCH("zzz",A:A)-10 Tried to incorporate same solution into =SUBTOTAL(103,A11:A5000) with =SUBTOTAL(103,(MATCH("zzz",A:A)-10)) and...
7. ### Subtotal of Sum of Hours and Not Count

Excel Version = 365 OS = Windows Hi Everybody, Still New to the whole Power Pivot thing so please bare with me as I will have a lot of Obvious Questions that might seem stupid. Summary: I have Engineers that work on Work Orders, Each Work Order has a Start Date, Start Time and a End Date...
8. ### Subtotal Sumproduct?

Is it possible to use SUBTOTAL in conjunction with SUMPRODUCT? I'm using the following formula to calculate a weighted average price increase. =SUMPRODUCT(N5:N3400,AQ5:AQ3400)/SUM(AQ5:AQ3400) column N= price increase percentage column AQ= sales I would like to add SUBTOTAL to the formula in...
9. ### Subtotal Visible and Add Another cell from another column if the subtotal cell is less than 1

I am trying to add visible cells in a column. I'm using subtotal for this. Then if one of the cells within that column that is visible is less than 1 I was want to add a cell from a different column. I'm trying to make apples to apples of two columns, if that makes sense. I for some reason...
10. ### Use of LARGE, SMALL, MAX, MIN, SUM Functions in spilled arrays

Hi all, I would like to use functions like LARGE, SMALL, MAX, MIN and SUM in spilled arrays and get the values PER row instead of the total. For Example: x y =MAX(A2:B2) dragged down =MAX(A2:B5) 1 2 2 8 3 4 4 5 6 6 7 8 8 The only solution I...
11. ### Error in computed Subtotal in Range

hi again, I'm using set of VBA but does not compute the range properly! what seems to be the problem? i shall implement it in a loop sequence but the second computation is a bit off Range("A2:M" & LastRow).AutoFilter 1, "=" & "*DAMAGE TO PROPERTY" Range("B10") =...
12. ### Replacing OFFSET to sum up to max value from specific cell

Hello everyone, I am new to the board & thank you all in advance for the help. I have data for the time it takes each step in a manufacturing process & each step has a unique name that never repeats, as seen below, pasted in A1 corner. Steps time Start Point Sum of Steps Count 2800N1 5...
13. ### How to find usual credit card amounts by each day of the month?

[having trouble uploading sample data] The credit card period runs from the 6th of month 1 and ends on the 5th of month 2. Then, the payment is due on the 2nd of Month 3. Then, the 6th of month 2 runs to the 5th of month 3. etc. I am trying to find the typical credit card amount for each day of...
14. ### How to sum visible content in one column that contains specific text in another column.

Hello, I have this formula to count the number of rows that contain "US" =SUMPRODUCT((SUBTOTAL(3,OFFSET(L4,ROW(L4:L227)-ROW(L4),)))*(L4:L227="US")) I would like to modify this formula to sum the amounts in column H4:H227 that match this criteria in column L above namely containing US. Thank you,
15. ### Subtotal by Funding on one sheet but put the results on another sheet

What I'm trying to do is create a macro that does the following: 1. On the "By Transaction" worksheet sort by the Funding column . 2. Subtotal by Total Amount Due by a change of Funding 3. Copy the Funding and Total Amount Due Subtotal onto another sheet "By Funding" where there are other fields...
16. ### Add value of subtotal onto value of row

Hello all, I'm unsure if this is something that can be done. However, I'm wondering if it's possible to add a subtotal (from a measure) onto a row value? In the attached example, I would be looking to add the Inbound actual excl recharges value, with the inbound unallocated actual value on the...
17. ### Count max no of consecutive positive/negative value in a filtered column

Hello all, Excited for my first post. MrExcel has been extremely useful in my excel journey, even as a non member, and I couldn't be more thankful ? My question: I have a column with positive and negative values in sheet "Sheet1" (image below). I would like to count the max number of...
18. ### averageifs in filtered table

Hi, I am trying to calculate the average from a table using the averageifs function, because I only want to take into account the numbers between the lower and upper boundary (LB and UB). I know I need to use the subtotal function to not take into account the hidden cells. I could not find...
19. ### Power BI Custom Subtotals

Hi, I'd like to be able to create custom subtotals for say Total Revenue based on other values. I'm using the idea from the video linked below to do this using a SWITCH function. The irony is the normal values pull through okay, it's the [Total Rev] value that doesn't, which is strange as the...
20. ### SUMPRODUCT & SUBTOTAL with same criteria ("S") across two non-contiguous columns [D3 Activity] & [D4 Activity] to return count of [Name] in either

Hi, I have: SUMPRODUCT((tblStudentProgress[D3 Activity]=Satisfactory)*(SUBTOTAL(103,OFFSET(AW3,ROW(tblStudentProgress[D3 Activity])-MIN(ROW(tblStudentProgress[D3 Activity])),0)))) + SUMPRODUCT((tblStudentProgress[D4 Activity]=Satisfactory)*(SUBTOTAL(103,OFFSET(AW3,ROW(tblStudentProgress[D4...

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back