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.

Is there a question here?

texasalynn

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

He's been enthusiastically trumpeting his virtues in a thread in the SQL Server forum too.

-PatP

Array formulas are much more functional for 99.9999% of the reporting I've done in Excel. Just my $0.02

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.

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

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.

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...