# [EXCEL HEAVEN] Part 9: Preview – Using SumProduct Formulas to AutoGenerate Spreadsheet Cash Book Summary Reports [How I Built My Automated Cash Book/Bank Reconciliation Software]

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.

[IMPORTANT: This blog's contents are being updated following the transfer to www.tayosolagbade.com from my former domain - Spontaneousdevelopment.com. As a result, some parts of it may not work properly for now. Quick Tip: If a link contains "spontaneousdevelopment.com", simply change it to "tayosolagbade.com" - and it should work. This applies to article links as well as image links. Work continues to update the links(in over 500 articles). Tayo K. Solagbade.]

# Practical Livestock Feed Formulation Handbook

Available as a PDF ebook, and also as a spiral bound print manual (from me). Click here for details.

You can also get it as an PDF ebook via my online store.

### N8,000.00 [Eight Thousand Naira] for persons who wish to send payment direct to my bank account.

Payment of N50,000.00 gets you the physical handbook and software on CD with videos etc, PLUS practical one-on-one, in person training with me at a feed mill in Lagos, Nigeria.

Once you submit the form below, you will be taken directly to the downloads page, just as an auto-response message will be delivered to your inbox, with links to the promised gifts.

I look forward to meeting and/or working with you!

Performance Improvement Specialist & Multipreneur
*Best Practice Farm Business Support Specialist
& Founder of the MS Excel Heaven Visual Basic Automation Club & Competition(www.excelheaven.biz)

# Excel-VB Driven Ration Formulator

2. Click here to watch a 4 part video in which I demonstrate how to use this app to formulate rations using real life data sent to me by an Algerian PhD student.

# EXCEL-VB DRIVEN POULTRY LAYER FARM MANAGER SOFTWARE

Click here to download a detailed PDF user guide and watch 15 screen shot user guide tutorials of the Monthly Poultry Farm Manager that I now offer Farm CEOs.

Name:
Email:

This post first appeared on …a Multi-disciplinary Blog For People Passionate About Reaching Their Goals!, please read the originial post: here

# Share the post

[EXCEL HEAVEN] Part 9: Preview – Using SumProduct Formulas to AutoGenerate Spreadsheet Cash Book Summary Reports [How I Built My Automated Cash Book/Bank Reconciliation Software]

×