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

    Unanswered: Reporting with Excel: the magic function

    This function (SUMPRODUCT) 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.

    I would not call myself an expert if I didn't know about SUMPRODUCT.

    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
    Jun 2002
    Location
    Houston, TX
    Posts
    116
    Is there a question here?

    texasalynn

  3. #3
    Join Date
    Mar 2004
    Location
    Minnesota, USA, Earth
    Posts
    65

    No, he's just advertising.

    No, he's just advertising. Feel free to ignore.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    He's been enthusiastically trumpeting his virtues in a thread in the SQL Server forum too.

    -PatP

  5. #5
    Join Date
    May 2004
    Posts
    9
    Array formulas are much more functional for 99.9999% of the reporting I've done in Excel. Just my $0.02

  6. #6
    Join Date
    Dec 2003
    Location
    Ottawa, Canada
    Posts
    72
    Quote Originally Posted by Pat Phelan
    He's been enthusiastically trumpeting his virtues in a thread in the SQL Server forum too.

    -PatP
    Hi Pat

    I am not trumpeting MY virtues but the virtues of SUMPRODUCT. It is a truely amazing function to transform data into information and not many books talk about it. 99% of the people I deal with don't know about this function but after I show them its potential they start using it profusely. It is so friendly that they can start developing great management tools and get rid of their consultant including me. And when this happens, I feel that I have done my job.
    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

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pierrevbaexcel
    I am not trumpeting MY virtues but the virtues of SUMPRODUCT.
    i'm going to have to disagree with this statement

    go back over all your posts here at dbforums, pierre

    how many of them contain examples of SUMPRODUCT versus justify your reasons for posting?

    if it's true that you just want to help people learn about SUMPRODUCT, then just tell them about SUMPRODUCT and skip the justification about how great an expert you are
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2004
    Location
    Minnesota, USA, Earth
    Posts
    65

    Pierre,

    Pierre,

    The point of these forums are for people to post obstacles they have run into and to post known solutions to these obstacles.

    When a user posts something that isn't a question, but proposes the virtue of a formula and then posts a link to their website touting how well they use the formula, well it just smells of advertising and self-promotion. I can't speak for others, but I don't want to go to a forum when I'm looking to be helpful and to look for help, but instead find self-promotion and advertisements. I can go to Google/Yahoo/Lycos/MSN/etc for that.

    My $0.02.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So its a powerful function and nobody uses it. Want to know why? Because you can do the same thing and much much more, much much easier with SQL. That's why.

    "With it you can sum a column (amounts) based on ANY number of criteria located in other columns (account, city, product,...). "???

    As SELECT statements go, that's pretty basic stuff...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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