subtotal

Struggling to get this to work. I have a SUMIFS that works fine: =SUMIFS(SiteInfo[Number of buildings],SiteInfo[Country], "=United States of America (USA)") But I want to exclude hidden rows, and from some research, SUBTOTAL seems to be the answer. But I can't get it to work with my SUMIFS. I...

Hello - I have a sheet full of billing groups and their accounts, with account values and billable amount. In some cases, there is only one account in a billing group, but most of the time, there are multiple accounts within the same group. I'm using VBA to calculate a subtotal of both the...

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...

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...

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...

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)...

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...

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...

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...

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...

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...

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...

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") =...

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...

[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...

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,

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...

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...

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...

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...