If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Excel > Reporting with Excel: the magic function

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 06-23-04, 09:00
pierrevbaexcel pierrevbaexcel is offline
Registered User
 
Join Date: Dec 2003
Location: Ottawa, Canada
Posts: 72
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
Reply With Quote
  #2 (permalink)  
Old 06-24-04, 11:57
texasalynn texasalynn is offline
Registered User
 
Join Date: Jun 2002
Location: Houston, TX
Posts: 116
Is there a question here?

texasalynn
Reply With Quote
  #3 (permalink)  
Old 06-24-04, 14:04
SR22Mike SR22Mike is offline
Registered User
 
Join Date: Mar 2004
Location: Minnesota, USA, Earth
Posts: 65
No, he's just advertising.

No, he's just advertising. Feel free to ignore.
Reply With Quote
  #4 (permalink)  
Old 06-24-04, 14:36
Pat Phelan Pat Phelan is offline
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,606
He's been enthusiastically trumpeting his virtues in a thread in the SQL Server forum too.

-PatP
Reply With Quote
  #5 (permalink)  
Old 06-24-04, 18:43
akamp akamp is offline
Registered User
 
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
Reply With Quote
  #6 (permalink)  
Old 06-25-04, 06:03
pierrevbaexcel pierrevbaexcel is offline
Registered User
 
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
Reply With Quote
  #7 (permalink)  
Old 06-25-04, 06:57
r937 r937 is offline
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
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
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #8 (permalink)  
Old 06-25-04, 14:56
SR22Mike SR22Mike is offline
Registered User
 
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.
Reply With Quote
  #9 (permalink)  
Old 06-25-04, 18:56
blindman blindman is offline
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 11,726
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"
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On