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.

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?)

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

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.

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

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

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

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

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

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

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

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

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

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

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.