1. A

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

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

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

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

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

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

    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,
  8. M

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

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

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

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

    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...
  13. Wad Mabbit

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

    Pivot Table (from Database): count total of grouped items

    Hi everyone, Is it possible to display the number of grouped items as a value or as a bracketed suffix to the name? For example: Accessoires would show "Accessoires (12)" and Accessoires Indoor would be "Accessoires Indoor (39)" even though it is collapsed. Alternatively, B3 would show the...
  15. A

    Subtotal above the pivot table

    Hello Guys, I have a Pivot table, where i need to add new row above the pivot table and show the subtotal of each column till last available Column. (Pivot range may vary) Below is the code which i tried but it was not working correctly. Please help. LR = ActiveSheet.Cells.Find("*"...
  16. D

    Excel Formula Help

    Afternoon All I've cobbled together the following formula to give a Subtotal with multiple options when a column is filtered but it seems to be counting some things twice. I'm sure there is a far more elegant formula I could use but the one I am using is below...
  17. F

    Exclude year with sumproduct and subtotal

    Hey guys, I've recently started to use the sumproduct and subtotal functions to make my data responde to filters and after some struggle i made it work, but right now i can't use an expression that excludes by year. So i need an expression that gives me the number of dates in a column that...
  18. F

    Countif with filters

    Hey guys, I've been trying to use a countif expression that allows me to check for misplaced data, which is pretty easy, however it becomes a lot more tricky when i try to make that same data respond to filters. This is the expression i have used so far COUNTIFS('A...
  19. F

    subtotal with countif

    I NEED SUbtotal with countif in Column D2 When i filtered in columnA give me 0 count in cell d2. ColumnA <colgroup><col><col><col></colgroup><tbody> ColumnB ColumnC Silver South-2 3 Silver South-2 3 Silver South-2 3 Silver South-2 0 Silver South-2 3 Silver South-1 3 Silver...
  20. faeryluv


    I'm using the Subtotal function, and that works good for what I'm doing, however, I don't need all the extra rows. Without the Subtotal function, I'm manually adding up the duplicate orders and then deleting the extra rows. Example Row 1: route 1 item D4 order 50 Row 2: route 1 item D4 order...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

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
Allow Ads at MrExcel

Which adblocker are you using?

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