+ Reply to Thread
Results 1 to 12 of 12

SUM/IF/FREQUENCY to replace "helper" column?

  1. #1
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    SUM/IF/FREQUENCY to replace "helper" column?

    Hi all,

    I'm struggling to SUM certain numbers based on several criteria, without using a helper column. It makes the file unnecessarily big and messy, but I cannot figure out how to work around it.

    Would such a calculation work with a FREQUENCY array formula? Could anyone have a look at my sample attached?

    The RED cells are the helper cells that I want to get rid of, where I currently do some pre-calculations to get to the final results (YELLOW).
    Attached Files Attached Files

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: SUM/IF/FREQUENCY to replace "helper" column?

    f4
    Please Login or Register  to view this content.
    Try this formula and copy towards right
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: SUM/IF/FREQUENCY to replace "helper" column?

    Just FYI, helper columns can actually make a file smaller and easier to understand, compared to using some involved complex formulas that can be hard to trouble shoot/modify/adjust later, when you have forgotten what they mean. Helper columns may take up less processing power and space than array functions, as well
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: SUM/IF/FREQUENCY to replace "helper" column?

    Not clear
    What do you calculate?

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: SUM/IF/FREQUENCY to replace "helper" column?

    I agree with the FDibbins post3.
    any way if you don't want to like the helper column use below formula (the earlier formula will not work for because I was inserted rows/columns at my end)
    E4=IFERROR(SUMPRODUCT((E$7:E$409="x")*SUMIFS(INDEX(IA!$A:$ZZ,0,MATCH(E6,IA!2:2,0)),IA!$A:$A,$E$2,IA!$C:$C,Attribute_PricePoint,IA!$B:$B,Attribute_RetailClass)),0)
    try this and copy towards right
    see the attache file
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: SUM/IF/FREQUENCY to replace "helper" column?

    Quote Originally Posted by nflsales View Post
    f4
    Please Login or Register  to view this content.
    Try this formula and copy towards right
    Your formula works! it calculates just what I asked for. Unfortunately just as FDibbins pointed out, it does make the file a lot slower as calculation time increases. I generally have bad experience with using SUMPRODUCT and try to use SUM/IF/FREQUENCY or SUMIF/COUNTIF where possible. Is there a way to use such formula here instead as well??

  7. #7
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: SUM/IF/FREQUENCY to replace "helper" column?

    Quote Originally Posted by FDibbins View Post
    Just FYI, helper columns can actually make a file smaller and easier to understand, compared to using some involved complex formulas that can be hard to trouble shoot/modify/adjust later, when you have forgotten what they mean. Helper columns may take up less processing power and space than array functions, as well
    I would literally have hundreds of helper columns, though, I agree that it does usually help to make it faster or even less confusing. It always depends on the particular case I guess...

  8. #8
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: SUM/IF/FREQUENCY to replace "helper" column?

    As per my knowledge the below is the simple formula to get the result as per your requirement.
    please don't select entire column from IA sheet

    E4=IFERROR(SUM(IF(E$7:E$409="x",SUMIFS(INDEX(IA!$A3:$ZZ410,0,MATCH(E6,IA!2:2,0)),IA!$A3:$A410,$E$2,IA!$C3:$C410,Attribute_PricePoint,IA!$B3:$B410,Attribute_RetailClass),0)),0)
    The above is an array formula, please confirm with Shift+Ctrl+Enter

  9. #9
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: SUM/IF/FREQUENCY to replace "helper" column?

    generally speaking your solution is excellent, it does what it's supposed to, but I'm pretty confident that there's a more efficient way to do it...

    e.g.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    above formulas get the exact same result, though, the latter is 100x (!) faster then the SUMPRODUCT approach.

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: SUM/IF/FREQUENCY to replace "helper" column?

    I have to agree with the rest of the crew. The helper system you have is the way to go.

    You mention using FREQUENCY. It's a fast function. It has limitations. It returns an extra row in the array that define the bins. That has to be accounted for in all the dependent and interacting formulas/ranges. That would be a maintenance nightmare by itself.

    FREQUENCY is primarily a counting and "locating" function. We occasionally sum the counts. What you require is a sum of sums or sum of products.

    As powerful as FREQUENCY is I don't think it's the tool for this job. If it were me I would stick with what you have.

    I hope this helps.
    Last edited by FlameRetired; 01-18-2018 at 12:17 AM.
    Dave

  11. #11
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: SUM/IF/FREQUENCY to replace "helper" column?

    Thanks everyone!!

    I was hoping for an alternative to SUMPRODUCT such as SUMIF not necessarily FREQUENCY, but it seems as if there's no other way...

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: SUM/IF/FREQUENCY to replace "helper" column?

    You're welcome. Thanks for the feedback.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Populate a drop down list using a formula without a "helper" Column
    By GarethmMorgan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-02-2017, 01:43 AM
  2. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  3. [SOLVED] vba code to replace the column names for those column name "Proposal that starts with "1."
    By mvneema in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-31-2014, 01:21 PM
  4. [SOLVED] VBA help needed to remove all "/" then replace with "-" from cell "B3"and "B5"
    By krjoshi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-17-2014, 02:11 PM
  5. [SOLVED] Replace all BLANK cells in column with header title "Balance" to "0"
    By ks100 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-21-2014, 09:25 AM
  6. Replies: 3
    Last Post: 02-11-2014, 05:36 PM
  7. [SOLVED] If there is any text in column "A$" on "sheet1" then move cell to column "A$" on "sheet2"
    By ckgeary in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-27-2013, 08:28 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