+ Reply to Thread
Results 1 to 7 of 7

how to sort table with subtotal/sumif values in row, and preserve formula integrity

  1. #1
    Registered User
    Join Date
    10-11-2016
    Location
    Switzerland
    MS-Off Ver
    Office 2013
    Posts
    4

    how to sort table with subtotal/sumif values in row, and preserve formula integrity

    Attached it comes a picture, and if you open it, you can read my issue explained in a more easy way to understand

    http://www.excelforum.com/attachment...1&d=1476193061

    Summarizing in words, i have a range of cells with in column A and C criteria (product type and group name) and in column D,E,... the values that i want to sum whenever product and group are the ones indicated
    I want the sum to be a row of the table, a kind of subtotal, and i know that the table can be sorted therefore each row can change relative position

    I can use sumif on the whole table specifying as sum_range only column D, however i am unable to avoid sumif to consider its own cell in the table range, therefore i get the message of circular reference
    Maybe mixing together array, indirect, vlookup and sumif functions, it is possible. Can someone find the solution?

    The sample file is also attached: http://www.excelforum.com/attachment...1&d=1476193061
    Many thanks!
    Paolo
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: how to sort table with subtotal/sumif values in row, and preserve formula integrity

    The example looks like it came from a pivot table, so we need to unscramble (normalize it) it back to its source data.

    The code does this it makes one record per group, per country, per variable, per year with its sales.

    Then we make a pivot table off of that.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    10-11-2016
    Location
    Switzerland
    MS-Off Ver
    Office 2013
    Posts
    4

    Re: how to sort table with subtotal/sumif values in row, and preserve formula integrity

    Hello Dflak,
    thanks first of all for your time!
    the example does not come from a pivot table, it is a master table that i designed to facilitate pivot operations as well as filtering on the table itself
    the table can be sorted and filtered manually from users and there are subtotals useful whenever i print or for discussion
    the problem is that the subtotals are adding together a range of values, but as soon as i sort the tables, the ranges get mixed up and the subtotals do not work anymore
    That's why i was looking for a solution which could have subtotals kind-of-cells able to find the values to add together, regardless of the sorting of the whole table
    i was thinking of nesting a vlookup in a sumif function, but then i got into the problem of how to avoid the cell of the subtotal itself in the whole column that the function is supposed to scan
    Hope i explained my issue better
    Thanks!
    Paolo

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: how to sort table with subtotal/sumif values in row, and preserve formula integrity

    Can you give me some more information on how you sort the tables and what you mean by mixing up the range of values?

  5. #5
    Registered User
    Join Date
    10-11-2016
    Location
    Switzerland
    MS-Off Ver
    Office 2013
    Posts
    4

    Re: how to sort table with subtotal/sumif values in row, and preserve formula integrity

    Hello Dflak, thanks for your willingness to help!!
    I have prepared a simpler and (hopefully) more clear picture where i explain my issue and my need.
    don't know why but if i click reply to your message, the browser just freeze, so i have to use the quick reply option where i cannot paste pictures, so i have linked it
    Here it is: https://www.dropbox.com/s/xco3yb2wi6...sheet.png?dl=0
    Thanks again!
    Paolo

  6. #6
    Registered User
    Join Date
    10-11-2016
    Location
    Switzerland
    MS-Off Ver
    Office 2013
    Posts
    4

    Re: how to sort table with subtotal/sumif values in row, and preserve formula integrity

    I solved it myself! Really thanks anyway, being forced to explain the issue helped me to find the solution using a simple SUMIFS function!

  7. #7
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: how to sort table with subtotal/sumif values in row, and preserve formula integrity

    That happens to me a lot . Glad you got it sorted out.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Sort Pivot Table by Column Subtotal
    By Tayque_J_Holmes in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-20-2016, 02:08 PM
  2. How to preserve formula references during sort in Microsoft Excel
    By Jan Zitniak in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-23-2013, 03:25 AM
  3. Macro to copy pivot table as values and preserve all formatting
    By Gary1980 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-28-2012, 09:23 AM
  4. Sort cell reference integrity
    By haulinthechilli in forum Excel General
    Replies: 3
    Last Post: 12-08-2010, 03:19 PM
  5. Sort function - line integrity compromised
    By marsseen in forum Excel General
    Replies: 3
    Last Post: 06-22-2009, 12:38 PM
  6. Replies: 3
    Last Post: 08-01-2007, 01:09 PM
  7. [SOLVED] Formula Integrity Not Preserved During Sort in Excel 2000
    By Kevin in forum Excel General
    Replies: 1
    Last Post: 04-15-2005, 06:06 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1