Using SUMPRODUCT to create a Conditional Weighted Average in Excel

Follow this article to learn how to use this resourceful function to create a Conditional Weighted Average in Excel

Background

One of the first articles I wrote when I first started writing tutorials was on the usage of SUMPRODUCT.

The original article covered its primary usage (multiplying arrays of data) along with how it could be used to create a weighted average – but the focus really was on how to use it to do conditional summing where there’s more than one condition.

I said at the time that SUMPRODUCT was one of the most useful yet underused formulas out there, and whilst times have changed a little, I still think it’s true.

The original article was written primarily to cover the ability to do conditional summing, back when Office 2003 was the primary suite in the business I worked for. Office 2007 saw the introduction of new formulas which replicated this behaviour, and with 2010 and 2013 since – there are few people still using 2003.

That being said, SUMPRODUCT remains an extremely powerful formula and was called into action when a colleague needed help with a weighted average formula.

Rather than write an entirely new article though, I’m going to essentially re-print the original article (with a few changes).

So what is SUMPRODUCT?

SUMPRODUCT is an Excel Function that is/was designed for multiplying arrays (sets/columns) of data against each other.

However, because of how Excel handles Boolean (true/false) values, it can be used for much more than this, and as such, is often regarded as one of the most useful, yet under-used, formulas in Excel.

Multiplying an Array of Data

So let’s start with the basics

Say you have two columns of data – a Stock Units, and a Cost Price column.

You want to know the cost value of all your stock.

You *could* multiply the cost price by stock units for each row, then add up the results.

Or, you multiply the entire column for stock by the entire column for cost price. SUMPRODUCT lets you do this.

=SUMPRODUCT(A2:A7*B2:B7)

= £11,097

Weighted Average

So let’s try a weighted average (we’ll get to conditional weighted averages in a moment – sit tight!)

If you wanted to know the average Cost Price of our stock, we would typically do

 =AVERAGE(B2:B7) 

= £1.35

However, this isn’t very reflective of the mix of stock we actually hold. SUMPRODUCT can help us here.

 =SUMPRODUCT(A2:A7*B2:B7)/SUM(A2:A7)

= £2.25

This a more reflective number, as it takes into account that we have a line with 4151 units @ 2.5

Conditional Summing

(Note – this is where you’d probably want to use SUMIFS / COUNTIFS instead)

Something a bit more useful now.

What if we wanted to know the amount of stock we have in NonFood?

Simple – we use a SUMIF() Formula

=SUMIF(A2:A7,"NonFood",C2:C7)

= 4,748

But, what if we wanted to know how much NonFood stock we have in the UK?

SUMIF can’t look at more than one criteria. However, as I already mentioned, because of the way Excel handles true/false values, you can achieve this using SUMPRODUCT

=SUMPRODUCT((A2:A7="NonFood")*(B2:B7="UK")*(C2:C7)) 

= 597

And of course, you could do things like ‘Food & Cost Price Greater than £1;

=SUMPRODUCT((A2:A6="Food")*(D2:D6>1)*(C2:C7))

= 125

And, you can have pretty much as many conditions as you like

Conditional Weighted Averages

You can of course combine the Weighted Average and Conditional Summing approaches to give you a conditional Weight Average

Simply add another condition to both ‘sides’ of the Weighted Average formula.

(using the same data set as above)

=SUMPRODUCT(C2:C7*D2:D7*(A2:A7="Food"))/SUMIF(A2:A7,"Food",C2:C7)

= £1.9

This will give the weighted average for all ‘Food’ stock

Advanced Conditional Summing

Ok, a bit more complex this one, but very useful.

What if, you want the Period 2 sales, for Food?

Obviously, =SUM(G3:J3) works, but if you want to your formula to be dynamic, eg, change for different period, various Categories, then this isn’t practical. SUMPRODUCT can help.

=SUMPRODUCT((A4:A5="Food")*(B1:J1="PD 2")*(B3:J5)) 

= 135

Notes

• The biggest issue with SUMPRODUCT is that it’s a system-heavy formula. If you use a lot of them against large data sets, you will find your file becomes slow
• SUMPRODUCT can be quite fussy at times
  • there must be no error values in your data (eg #DIV/0)
  • In earlier versions of Excel (eg 2003 & earlier) You can’t reference the entire column (eg A:A) – must specify the column numbers (eg A1:A65000)
  • All your column ranges must be the same size (same start/end row)
  • Don’t include your column header row in the ranges

     

3 comments