+ Reply to Thread
Results 1 to 15 of 15

formula to sum up values of non-sequential columns

  1. #1
    Registered User
    Join Date
    12-26-2012
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2003, Excel 2010
    Posts
    38

    Question formula to sum up values of non-sequential columns

    I have a raw data in excel that contains more than a hundred columns and around 35K rows transaction details, I need a solution to summarize the data, some columns contain a series of title like ineligible item 1 to 30, ineligible amount 1 to 30 (USD), ineligible amount 1 to 30 (IDR). the column placement in the file is like:
    ineligible item 1, ineligible amount 1 (USD), ineligible amount 1 (IDR), ineligible item 2, ineligible amount 2 (USD), ineligible amount 2 (IDR) and continue to 30. most of the cells filled with "null" and the rest is filled with text and currency.
    I have no idea how to sum the amount (USD) and (IDR) based on trx id, ineligible item total and ineligible item by group.
    here I attached a sample file to be more clear with the problem. I only put 3 rows of raw data and 10 series of non-sequential columns to make file smaller. raw data will be in RAW sheet, and summary sheet that need to fill. I wonder what formula combination to use. I need to summarize it ASAP, it will take days to do it manually.
    please forgive my English.
    Attached Files Attached Files

  2. #2
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: formula to sum up values of non-sequential columns

    Hi

    Have you tried pivot tables? I suppose you need that or even PowerPivot/PowerQuery

  3. #3
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: formula to sum up values of non-sequential columns

    Assuming that Trx ID will be unique in the raw data, try this in summary D3, fill right and down as needed.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    12-26-2012
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2003, Excel 2010
    Posts
    38

    Re: formula to sum up values of non-sequential columns

    Thank you for your reply, i will try the formula as soon as i turn on my laptop again, it's already 3.00 in the morning here. But for sure trx id not really unique, in some cases it may appear 2 or 3 times in raw data with different data as additional of previous ineligible items.
    I will update the result here.

  5. #5
    Registered User
    Join Date
    12-26-2012
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2003, Excel 2010
    Posts
    38

    Re: formula to sum up values of non-sequential columns

    I will give a try but i think i prefer to use formula because i can give the formula to other team members easily instead of teaching them to use pivot tables

  6. #6
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: formula to sum up values of non-sequential columns

    Quote Originally Posted by ruliansyah View Post
    I will give a try but i think i prefer to use formula because ....
    Yes, yes, try first and think later, in the order you have in your sentence, because summarizing with pivot tables is better.

  7. #7
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: formula to sum up values of non-sequential columns

    The formula that I suggested will not be of any use with repeated trx id's. Looking at your sample file again, I don't think that a formula or pivottable will be up to the task.

    Is your user profile correct with your version of excel? If not, please update it. It might be possible with power query, but I'm not sure if your listed version of excel supports this.

  8. #8
    Registered User
    Join Date
    12-26-2012
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2003, Excel 2010
    Posts
    38

    Re: formula to sum up values of non-sequential columns

    Thank you mr. jason.b75, I've checked your attachment, it works well for the ineligible amount totals but not working for the details on column L to Y. I'm trying to modify the formula but not yet succeed.
    some of my team use excel 2013 and the rest excel 2016.

  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,499

    Re: formula to sum up values of non-sequential columns

    Pl upload file with more data in Raw sheet and show expected results in in all columns of Summary sheet.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  10. #10
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,591

    Re: formula to sum up values of non-sequential columns

    I suspect you could achieve with SUMPRODUCT however, given size of real dataset the performance impacts will be significant.

    For ex., using your sample file:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    would generate a number of results, which would be along right lines however, I wouldn't say this is a pragmatic / sustainable approach -- especially when extending scope to include all the calcs to generate the entirety of the table (SUMPRODUCT not appropriate for the simpler ones).

    given the layout of the (raw) data you would be best served using either VBA to summarise (should be fast if coded well) or, if possible, PowerQuery -- there a few folk very adept here at PQ, alas I am not one of them!
    Last edited by XLent; 06-14-2019 at 03:42 AM. Reason: typo in narrative

  11. #11
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: formula to sum up values of non-sequential columns

    Quote Originally Posted by ruliansyah View Post
    it works well for the ineligible amount totals but not working for the details on column L to Y. I'm trying to modify the formula but not yet succeed.
    This is why I said that a formula will not be suitable, I overlooked the difference in the layout of the raw data relating to those columns.

    I've managed to produce the correct output (I think I have anyway) using power query, however it is not a straightforward process. I had to manipulate the data in PQ, then export it back to excel, add another column with a formula, then manipulate a second time in PQ.

    It should be possible with a single query, if I can't work it out soon then I'll post the file with the double query method to see if anyone else can finish it off.

    edit:-

    PQ not going to work either, not enough rows in a worksheet to unpivot the data if you really do have 35k rows and 100 columns. (will need 3.5M rows, limit is 1048576).
    Last edited by jason.b75; 06-14-2019 at 12:43 PM.

  12. #12
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,591

    Re: formula to sum up values of non-sequential columns

    hi, minor update to my prior post as I realised (belatedly) that I wasn't handling the IDR values - so below revision - for illustration purposes:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    note: again, to reiterate, I am not championing the above, or any other formula based approach
    i.e. if you can use PQ do so, else VBA, as a calc based approach will be slow given size & layout of your dataset...

  13. #13
    Registered User
    Join Date
    12-26-2012
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2003, Excel 2010
    Posts
    38

    Re: formula to sum up values of non-sequential columns

    Thank you all, i'm so sorry for late response, i currently having a little trouble with my little boys so i cannot use my laptop nor go to work but i will try all the suggestions here ASAP. For sure i believe VBA can solve this but it will take time for me to create the VBA.
    I reply this on my mobile phone so i cannot try all suggestion directly. I will update again as soon as i have a change to use a laptop.

  14. #14
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: formula to sum up values of non-sequential columns

    If you're going to insist on formulas, then you will need to keep them as simple as possible, some assumptions and sacrificing consolidation of duplicated trx id's will help, but not much.

    In terms of the most practical method,

    First choice:- Use VBA
    Second choice:- Still VBA
    Third choice:- Yep, VBA again.
    ....Nine hunded and ninety ninth choice:- maybe now think about these formulas, but I would still prefer VBA.
    In Summary sheet
    A3, fill right to E3 and down as needed.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    H3, fill down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I3, fill down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    L3, Don't fill yet!
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    M3, Don't fill yet!
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Select both L3 and M3 together, then drag right to Y3 (you need to drag as a pair for them to alternate when you fill), then fill down.
    Wait a week or so for it to calculate

    I've updated your sample file with the above formulas in place.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    12-26-2012
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2003, Excel 2010
    Posts
    38

    Re: formula to sum up values of non-sequential columns

    Hello, Sorry for this late reply, I tried some of suggestions here and I found out the combination of solutions given by mr. jason.b75 quite helpful, I think I will use those formulas for now since we are running out of time, I will try to create VBA after this one done.
    Thank you so much for everyone here. I really appreciate it.

+ 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. VBA Copy from sequential workbooks into a single workbook with sequential columns
    By YeknomDude in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-03-2017, 01:40 PM
  2. Applying Formula to assign numeric values to text values in sequential order
    By jmshanahan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-15-2016, 03:04 PM
  3. [SOLVED] Column Offset Formula - Sequential Columns Target
    By sl729 in forum Excel General
    Replies: 3
    Last Post: 05-21-2016, 09:03 PM
  4. Evaluate columns for sequential numbers and roll up
    By vlock22 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-01-2015, 01:53 PM
  5. [SOLVED] copy sequential columns / paste to non-sequential columns
    By macrorookie in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-09-2014, 04:59 PM
  6. [SOLVED] Changing number values into sequential text values.
    By alex.l_91 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-17-2013, 02:37 AM
  7. formula for non-sequential columns
    By akonealive@gmail.com in forum Excel General
    Replies: 4
    Last Post: 05-17-2006, 07:45 PM

Tags for this Thread

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