+ Reply to Thread
Results 1 to 5 of 5

AR Aging Report - Subtotal formula for visible rows only

  1. 08-22-2019, 08:52 PM #1
    KapitanDan
    KapitanDan is offline
    Registered User
    Join Date
    08-22-2019
    Location
    Winnipeg, Canada
    MS-Off Ver
    2018
    Posts
    2

    AR Aging Report - Subtotal formula for visible rows only

    Hi I need help in creating a formula that will help me show the total value per each criteria for each filtered data. Because in my aging report I want to show the total amount of all companies and show subtotals of different "Status" of each invoice. I have been trying to figure out the subproduct formula but unsuccessful.

    Apologies if this was already answered before, if so please send link or point me to the right direction. New to this forum and thank you again.

    Example:

    Capture_Unfiltered.JPG

    Capture_Filtered.JPG

    Your help is greatly appreciated.
    Last edited by KapitanDan; 08-22-2019 at 08:53 PM. Reason: Explaining my post
    Reply With QuoteRegister To Reply
  2. 08-22-2019, 11:19 PM #2
    tranhieu04
    tranhieu04 is offline
    Registered User
    Join Date
    08-09-2019
    Location
    Ha nam, Vietnam
    MS-Off Ver
    2010
    Posts
    13

    Re: AR Aging Report - Subtotal formula for visible rows only

    hi,
    you can see the method of Subtotal formula.
    total value (only show item after filter) by subtotal(9, range total)
    Ex: SUBTOTAL(9,AC6:AC10)
    Reply With QuoteRegister To Reply
  3. 08-23-2019, 08:31 AM #3
    KapitanDan
    KapitanDan is offline
    Registered User
    Join Date
    08-22-2019
    Location
    Winnipeg, Canada
    MS-Off Ver
    2018
    Posts
    2
    Quote Originally Posted by tranhieu04 View Post
    hi,
    you can see the method of Subtotal formula.
    total value (only show item after filter) by subtotal(9, range total)
    Ex: SUBTOTAL(9,AC6:AC10)
    I know that. What I need is once I filter per company it’ll also show the amounts per status at the top. Also subtotal accordingly. It’s like subtotalling 2 criterias.
    Reply With QuoteRegister To Reply
  4. 08-23-2019, 09:01 AM #4
    tranhieu04
    tranhieu04 is offline
    Registered User
    Join Date
    08-09-2019
    Location
    Ha nam, Vietnam
    MS-Off Ver
    2010
    Posts
    13
    Befor i did'nt understood. I think you can use 2 method to soler it.
    1. By company filter of status you use a cells to filter company (drop and select) then vlookup that to show status.
    2. Use vba

    That i think to solve. Maybe have most simple...
    Reply With QuoteRegister To Reply
  5. 08-27-2019, 10:35 PM #5
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,548

    Re: AR Aging Report - Subtotal formula for visible rows only

    Hello Kapitan Dan and Welcome to Excel Forum.
    You could possibly use the strategy that is described in post #17 of the linked thread.
    https://www.excelforum.com/excel-gen...a-table-2.html
    To have someone attempt to apply that method to your data, please upload the spreadsheet from which the screen shots in post #1 are taken by clicking on the GO ADVANCED button below the Quick Reply window and then scrolling down to Manage Attachments to open the upload window.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Over due / Aging report formula
    By Shan54321 in forum Excel General
    Replies: 1
    Last Post: 02-22-2019, 03:31 AM
  2. Help with Conditional Formula on Aging Report
    By BigTreeSmallAxe in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-20-2018, 04:29 PM
  3. Aging report formula not working for 0-30 days past due
    By Lorir1 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-04-2015, 01:12 PM
  4. Aging Report, formula in pivot or column in data
    By andy.k in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-25-2014, 11:28 AM
  5. Formula for an Aging Report
    By wpwalsh in forum Excel General
    Replies: 2
    Last Post: 04-28-2012, 07:32 AM
  6. aging report
    By galarza in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 08-13-2009, 07:25 AM
  7. Visible rows and functions that work:subtotal function
    By Biff in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 09:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1