Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2003
    Location
    Ottawa, Canada
    Posts
    72

    Unanswered: The magic function: Reporting with Excel

    As a new member I will start by sharing my most important tip the SUMPRODUCT function

    This function ignored by many is the function that makes Excel the best reporting tool. It allows the user to transform any database extract into a report. With it you can sum a column (amounts) based on ANY number of criteria located in other columns (account, city, product,...). I am a full time Excel-VBA Online Consultant and I use it in 75% of the applications that I develop (even the ones with VBA). It is simple to use yet one of the most powerful function in Excel for Accountants, Financial Analysts and Data Analysts.

    There is an example on my website at:
    http://www.excel-vba.com/e-formula-sumproduct.htm

    Anybody else uses it.
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick
    www.excel-examples.com

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Yep, I agree with you. I have particularly found it helpful in multi-criteria sums, when SUMIF does not accept more than one condition.

    When array-entered, it offers even more. I even helped a co-worker revise his database so that SUMPRODUCT now has become his best tool for reporting to senior management.

    (I look forward to browsing your site. I noticed that you use * for the last condition. In some of my investigations, the comma "," for the last item seems to work faster. Have you done any tests on that?)
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Dec 2003
    Location
    Ottawa, Canada
    Posts
    72
    Hi, Shades

    Thanks for the comma tip. I don't know if it will work faster but it does make the formula more sympathetic.

    As for array, I have stopped using CTRL/ENTER because it didn't really add much to the function and in fact it became limited (change all or change nothing) and my users were annoyed by the extra.

    If SUMPRODUCT was more widely know, a lot of users would save a lot of money.

    Empowering all Excel users with SUMPRODUCT is a dream of mine. With this function all users can develop analysis and reports. Th Intelligence of the Business (the users) is made to contribute enriching substantially the decision making processes within enterprises.

    Regards

    Originally posted by shades
    Yep, I agree with you. I have particularly found it helpful in multi-criteria sums, when SUMIF does not accept more than one condition.

    When array-entered, it offers even more. I even helped a co-worker revise his database so that SUMPRODUCT now has become his best tool for reporting to senior management.

    (I look forward to browsing your site. I noticed that you use * for the last condition. In some of my investigations, the comma "," for the last item seems to work faster. Have you done any tests on that?)
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick
    www.excel-examples.com

  4. #4
    Join Date
    Aug 2003
    Location
    Phoenix, AZ
    Posts
    12
    You are correct in the statement that the Sumproduct function is ignored by many!!(mea culpa). I had actually never tried it before, so I went to your website and gave it a shot. Is pretty nifty. But does this give you any advantages over a Pivot Table. It seemed to do the same kind of thing, so I did a PivotTable on the sample data and it returned the same result(s), except the columns in the table were sorted alphabetically. I use Pivot Tables extensively in db extracts & imports, so I wonder what advantages the SUMPRODUCT function would give. The most obvious is for Macros/Scripts the "refresh" of the pivot table wouldn't have to be made on each new import. On the other hand, I did a single worksheet file for both a SUMPRODUCT and Pivot Table. Strangely enough, the pivot table was a smaller file (14K vs 10K), although I believe the Pivot Table should use a lot more memory which would be a drawback for large databases.

  5. #5
    Join Date
    Dec 2003
    Location
    Ottawa, Canada
    Posts
    72
    Hi, DCutler

    Pivot Tables are an analysis tool, SUMPRODUCT is a reporting tool. My clients are into reporting and you cannot create an interesting layout with PT.

    My clients also find SUMPRODUCT less complicated than PT. They copy/paste their data and their report is updated. With the use of drop-down lists in the heading they can even do dynamic reporting where a single template serves to issue hundreds of reports with a single copy/paste of all the data. There is an example in my Resource Workbooks that are on sale right now at $20

    Best Wishes for 2004
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick
    www.excel-examples.com

  6. #6
    Join Date
    Dec 2003
    Location
    Östersund Sweden
    Posts
    60
    Hi guys,

    Apologise for jumping in in this thread but:

    * Yes, the SUMPRODUCT can be useful especially for singel cases,
    i e used in one cell.

    * But as the numbers of SUMPRODUCT-formulas increase the more
    negative impact it will have on performance. This is the drawback with
    arrayfunctions / formulas.

    * There exist also good alternatives, especially working with large
    amount of data. The database-functions are very useful together with
    the Pivottable.

    Set up a 20x20 matris with SUMPRODUCT and You soon find out what I
    mean...

    Add it to Your toolbox but use it with good judgement

    Kind regards,
    Dennis
    Last edited by Xl-Dennis; 12-30-03 at 19:52.
    Kind regards,
    Dennis

  7. #7
    Join Date
    Dec 2003
    Location
    Ottawa, Canada
    Posts
    72
    Hi Xl-Dennis

    Welcome in the thread

    First, I do not use the array function (Shift/Ctrl/Enter) I enter it as any other formula. Much simpler to maintain.

    As I was telling DCutler, I use SUMPRODUCT for reporting. I bring the data into a "Data" sheet and the report on another sheet looks at the data through SUMPRODUCT.

    With todays computers and RAMs, you need to have thousands of formulas to start seeing calculation delays. I have built many spreadsheets with thousands of formulas without problems.

    Even if at a certain level. there is a few second delay it is not important because I am using SUMPRODUCT in reporting not in analysis.

    As for dynamic analysis, Pivot Tables are great but as my data is on a single sheet, I can use the database functions (filter, sort, subtotal) to validate my reports before I issue them they are much faster than the PT and more user friendly.

    Again, go see my website on the topic and download my Tutorials, you will see that there is a lot of potential in SUMPRODUCT and INDEX/MATCH formulas to do some real reporting with Excel.

    www.excel-vba.com

    Regards and Best Wishes
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick
    www.excel-examples.com

  8. #8
    Join Date
    Dec 2003
    Location
    Östersund Sweden
    Posts
    60
    Hi,

    I usually don´t argue about stuff like this but obviously we need here some clarifications.

    First of all I´m no newbie with XL and certainly does no further introduction to SUMPRODUCT

    Your are most welcome to read my contribution to the CellMaster here:
    http://www.interq.or.jp/sun/puremis/...ellmasters.htm

    There actually exist some built-in array-functions in XL

    Per se SUMPRODUCT is an array-functions although You don´t enter it with ctrl+shift+enter. FREQUENCY is another array-function.

    Create a SUMPRODUCT like the following:

    =SUMPRODUCT((A2:A6=1)*(B2:B6=4))

    Now activate the contents, highlight the first part, i e (A2:A6=1). Hit the F9-button and You will get something similar to:
    {TRUE;FALSE;FALSE;FALSE;FALSE}

    Reperat it but for the second part, i e (B2:B6=4) and You will get something similar to:
    {TRUE;FALSE;TRUE;FALSE;FALSE}

    In the next step we perform the multiplication where TRUE has the number 1 and FALSE the number 0.
    TRUE * TRUE = 1
    FALSE * FALSE = 0
    FALSE * TRUE = 0
    FALSE * FALSE = 0
    FALSE * FALSE = 0

    In the final step we sum it and get 1 (1+0+0+0+0)

    From a strictly technical point of view the SUMPRODUCT is a quite high Volatile-functions.

    End of todays lesson

    With todays computers and RAMs, you need to have thousands of formulas to start seeing calculation delays.
    I total disagree and I'm surprised over the fact that You rely on hardware when building clients-solutions.

    Please test Charles Williams nice add-in (which I´ve been a beta-tester for): http://www.decisionmodels.com/index.htm.

    Except for the tool he also give some valuable input when it come to optimising speed and memory & limitations - see:
    http://www.decisionmodels.com/optspeedj.htm

    As for the INDEX/MATCH this approach has some benefits but that's another story

    Kind regards,
    Dennis

    MrExcel MVP and OzGrid MVP
    Kind regards,
    Dennis

  9. #9
    Join Date
    Dec 2003
    Location
    Ottawa, Canada
    Posts
    72
    Hi Xl-Dennis

    This is very technical. I am first a Business Analyst and Excel is a just a tool. After 10 years of listening to my clients needs and learning from them about the best and worst ways to do things, I have selected a set of tools within Excel that empowers them.

    With all the so called Reporting Applications not doing exactly what my clients want, with all the add-ins offering too many useless things I have decided to share my knowledge rather than create tools that are "good" for everyone.

    My philosophy is showing peole how to fish rather than selling them fish.

    Reporting is a process that comes from intuition and it is very personal, by giving people tools I allow them to use their intelligence and they love it.

    Sometime, some of my clients need something complicated (with VBA) in which case, I will become very techie but I prefer coaching people rather than developing black boxes.

    I am a Business Analyst first so I don't get technical with my clients unless they ask for it. Next time, I will send them to you.

    Regards
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick
    www.excel-examples.com

  10. #10
    Join Date
    Dec 2003
    Location
    Östersund Sweden
    Posts
    60
    Hi,

    Yes, stuff can be very technical if we want. As I said in my earlier post I usually don´t discuss XL-related stuff in this way but it is some time necessary, on public forums like this, when certain aspects need to be corrected.

    Anyway, if You have the time and interest I believe You will find it interesting to take a deeper look into it.


    BTW, You can call me Dennis

    Season greeting to You all,
    Dennis
    Kind regards,
    Dennis

  11. #11
    Join Date
    Jan 2004
    Location
    california
    Posts
    6

    Re: The magic function: Reporting with Excel

    Hi,

    I applogies for this. I just want to say, I have seen ur site in earlier. It is really good, and I used learn more from u.

    Regards,
    GGL

    Originally posted by pierrevbaexcel
    As a new member I will start by sharing my most important tip the SUMPRODUCT function

    This function ignored by many is the function that makes Excel the best reporting tool. It allows the user to transform any database extract into a report. With it you can sum a column (amounts) based on ANY number of criteria located in other columns (account, city, product,...). I am a full time Excel-VBA Online Consultant and I use it in 75% of the applications that I develop (even the ones with VBA). It is simple to use yet one of the most powerful function in Excel for Accountants, Financial Analysts and Data Analysts.

    There is an example on my website at:
    http://www.excel-vba.com/e-formula-sumproduct.htm

    Anybody else uses it.

  12. #12
    Join Date
    Dec 2003
    Location
    Ottawa, Canada
    Posts
    72

    Talking

    Hi

    Thank you.

    Each time someone tells me that he has started using SUMPRODUCT, I consider it as one more step toward autonomy in reporting for the data analysts.

    It makes me happy
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick
    www.excel-examples.com

  13. #13
    Join Date
    Apr 2007
    Posts
    1
    Hi,
    This is an excellent discussion. I hope I don't bog you all down with this, but I am having a tough time bridging this to help me.
    I am trying to report data from Access in Excel. Right now I am playing with pivot tables. They're powerful and handy. However, they stink with reporting. The data moves around as my data base changes (the lines changes with different scenarios and the months(columns) changes). How can I tie down references into a pivot table?
    GETPIVOTDATA works to a point, but it's long and messy because I need nested 'if' statements.
    I'm having less success with SUMPRODUCT. It doesn't seem to work when I have multiple row sub headings to sort through.
    Is there another option to pivot tables? I think my Access database is too big to lay directly into Excel.
    Thanks in advance for any tips.
    DJ Grieshop

  14. #14
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy, and welcome to the board.

    Pivot table are very powerful, but you are correct - they are not very good for reports. As for the changing data, you can use dynamic named ranges on which to base the Pivot table. But Pivot tables are easier in VBA.

    The best solution I have found is to use VBA which does the following:

    1. construct the Pivot Table (also using dynamic named ranges)
    2. copy and PasteSpecial
    3. delete the Pivot Table
    4. format all data properly
    5. setup final report

    I have done this for some work and the whole process takes 15-20 seconds. And it really doesn't matter how large the underlying data is. I know who used this approach on a significant amount of data still had the whole process done in less than a minute. That's usually faster than someone can construct a Pivot table in Excel.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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