This menu driven Payments Summary Auto Reporting interface (see screenshot below) in my Automated Cash Book/Bank Reconciliation App (shown below) taps data it sums into totals from the Cash Book
Study the SUMPRODUCT formula in the yellow box shown below…
Take note of the syntax and the way range names are used in the formula.
Next week I will send out a step-by-step video based explanation that I’m currently recording, SHOWING how I used this powerful Sum Product Formula to generate summaries in the above shown Dynamic Auto Summary Payments Report that automatically computes totals for the different expense heads in the Impress Cash Book of the Excel-VB Driven Cash Book/Bank Reconciliation spreadsheet app we’re building in this tutorial series.
As with everything else practical, the best way to help you understand SumProduct formulas is to show them in use. So the video tutorial I’ll send out next will do that.
For this week, I’ll leave you with a few words about Sum Product formulas and how I first discovered them…
Most of those who have followed my work on Excel-VB Solutions Development will know that I have repeatedly mentioned one expert – Pierre LeClerc as being my role model, mentor and inspiration.
LeClerc, is much older than I am and boasts decades of custom spreadsheet programming and database solutions development experience.
His ability to communicate complex logic in simple, easy to understand language, made it easy for me to LEARN smart techniques from him, about how to get maximum results with minimal efforts, by making intelligent use of MS Excel and related applications.
It was LeClerc who pointed out in one of his excellent website articles that I read back in 2006/7, that “mastering SUMPRODUCT formulas and INDEX/MATCH formulas is of the utmost importance if you want to become an expert in reporting, a reporter able to extract, organise, analyse and present data.”
He also explained that the two compound formulas mentioned are so powerful that one might not even need to lean visual basic coding proper, in order to achieve significant improvement in the performance and ease of maintenance of Excel workbooks on a sustainable basis.
Those words really appealed to me, because as a performance improvement specialist, my focus had always been to develop smart time, effort and cost-saving ways to use PC and Internet technology to boost output in the workplace.
Based on the insights shared by LeClerc, I came to see mastery of the tools he described as being truly key to my ability to make money profitably as an Excel-VB Solutions Developer.
So, I began studying and using both tools, and ultimately became VERY proficient in their use. What’s more, I discovered that he was right, that knowing how to use them alone enabled me achieve seemingly magical outcomes/results in helping clients QUICKLY and EASILY generate useful reports for decision making, by “extracting, organising, analysing and presenting data”!
But I digress. Let me get back to introducing SUMPRODUCT formulas to those of you who may not be familiar with them, and their use.
SUMPRODUCT FORMULAS, as described by LeClerc, typically come in useful, when (and I quote him below):
“…you need a column (quantity or amount) based on the value of one or more criteria in other columns (date, account, product, city, department, country, sales rep., etc). The SUMPRODUCT formula does that very elegantly. The SUMPRODUCT formula is a “multi criteria conditional sum”. I have been using these formulas now for 5 years and I find new applications all the time…”they are in fact queries in a cell”
Next week, I’ll illustrate the power of SUMPRODUCT formulas in the step-by-step video I will email to all members of the Excel Heaven Visual Basic Club.
