Search

Sumproduct function to Consolidate Revenues?

Share

Chandoo.org is all about simplifying life using Excel. When I first started visiting the site, I was amazed at the amount of information on the site, which made your life easier. My next few posts would be about excel functions which can make your life for Financial Modeling easier! These are simple examples that you might find relevant in your daily life as well.

Let’s say your organization had 10 business segments, each segment selling different products at different prices. You have data available for the segments, how do you consolidate the revenues in one shot?

How do you model this in excel fast? In this tutorial we understand how you can use SumProduct() formula to do that!

What is the SumProduct() function

SumProduct() formula in excel, would take a couple of arrays (Say A and B) as inputs. Multiple corresponding items in each Array – A[1] x B[1], A[2] x B[2], … and sum them together.

SumProduct would get you the total revenue from a segmental breakup

The function is simple, it does all the difficult calculations for you and gets you the solution!

Traditional way of calculating consolidated Revenues

Step I: Getting the Segmental Revenues

Step II: Summing up the revenues

Consolidated Revenues using SumProduct()

Using the SumProduct() the consolidated revenues can be calculated in a single cell! It’s like the calculation is on fire!

Where else can this function be useful in Finance?

The function would see a lot of usage in the financial models. You can use it to consolidate revenues (Segmental volumes and prices to total revenues), calculate depreciations (when you have various assets and depreciation rates), consolidate costs (different raw material prices and volumes), NPV (Discount Rates and Cash flows), etc.

Are you also using SumProduct() function in Finance?

I have used SumProduct in my models. I find them to be very useful and speed up the modelling process. But sometimes I find that they confuse the readers of my model. Have you used the function in your models?

I have created a template for you, where the subheadings are given and you have use the functions to get the right values for you! You can download the same from here. You can go through the case and fill in the yellow boxes. I also recommend that you try to create this structure on your own (so that you get a hang of what information is to be recorded).

Also you can download this filled template and check, if the information you recorded, matches mine or not!

We are glad to inform that our new financial modeling & project finance modeling online class is ready for your consideration.

The article is written by Paramdeep from Pristine.

Chandoo.org has partnered with Pristine to launch a Financial Modeling Course. For details click here.

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

How to calculate WEEKNUMBER in Month / Quarter / Year with Excel?

Let’s say you have daily data and your boss wants to see the trends by week in month or week in quarter? How do you calculate the week number in a month or quarter? In this article, let me explain the logic and formulas we can use Excel for this.

Related Tips

Excel Howtos

Excel Howtos

Excel Howtos

Excel Howtos

Excel Howtos

Excel Howtos

9 Responses to “Sumproduct function to Consolidate Revenues?”

1. Cameron says:

There are several functions and techniques that I might leave out of a model for fear of being overcomplicated for my users to grasp, but Sumproduct is not one of them. It's an easy enough concept to grasp that it can be explained in one sentence (in most contexts), as such, I use it where it makes sense.

2. Stephen says:

My spreadsheets are nothing like the powerful and "sexy" things we see on this forum... simply because most of the people here struggle with =SUM(...)

That doesn't mean that I cannot use them whilst trying to educate the masses. However, there are 2 very useful functions that I have struggled to get others to use. INDEX and SUMPRODUCT. I guess everyone prefers the helper columns.

On SUMPRODUCT, I love the ability to make an:
OR statement (e.g. =Sumproduct(((array1=Criteria1)+(array2=Criteria2)),Sumarray)
AND statement (e.g. =Sumproduct(((array1=Criteria1)*(array2=Criteria2)),Sumarray)
(I hope syntax is right as my as my typing isn't great!)

3. Fred says:

Very often we tend to study, using the example shown above, the details of the information. For reporting and consolidation only purposes, sumproduct is invaluable. However, when it come to meetings/discussion most often then not we have to show and explain the how much revenue was generated by product X, Y and Z individually(% of total, trends, CY vs PY, CM vs PM, etc), showing the readers step by step how each number was derived.

And explaining something like sumproduct to skeptical managers or colleagues who don't know sumproduct and other less utilized function took more time than using the traditional way.

4. @Cameron: I agree. SumProduct is a really nice and useful formula.
@Stephen: Again a nice usage of SumProduct by using conditions. It would be great, if you can share an experience, where you used it in financial modeling (Context).
@Fred: Yes. If the management is looking for product wise analysis, then this function would hide the details.

5. David says:

Depending on how the data's categorized, I'll just used the Subtotal feature in excel for quick and dirty calcs.

Now if I'm creating a model that will be widely used on in a group, well that's another story.

6. [...] time we had discussed the use of SumProduct() to ease your life for calculation of consolidated revenues and depreciation. This time we would be [...]

7. Nora says:

Hi, Friend, I can't open the archive... Par-SumProduct.xls
(My English is so bad, but I can read it well !! )
Saludos
Nora

8. Article says:

Hi
Can anyone help me out if there is any formula to find out the cell combinations out of the sum. For eg. i hav a ready made total- say 132453.. bt in that column i need to permute nd combine various no.s to find 132453. Is there any such formula so as to find dese permuted & combined sum of cells directly..

Thankyou

Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.