The SumProduct feature in Excel is, I think, a fantastic formula that is so often under used. It allows you to multiply corresponding values in your chosen arrays, and then returns the sum of these. You’re probably thinking, but pivot tables do that as well. You are right to think that, but this way gives you so much more control, and especially on the formatting side of it for presentation purposes. SumProduct is sometimes referred as an array formula, as shown in image 2. It takes the arrays that you have chosen and and based on meeting certain criterea set by you provides a sum of these conditions.
I will use the example of perhaps a sales team (often used I know) and you want to know what each salesperson’s generated income is by lets says timeband and day of week. The SumProduct will have four arrays (Day, Time Band, Income, Name). Three of these will need a criterea to match with and the fourth will be the income values.
Click in a cell that you want to return the total in. And then type
=sumproduct(( and Excel will then provide a tool tip as shown in Image 2. Directly after the second opening bracket go to your first array and select it using the mouse. So for this it will be the Day column. Select cell A2 and then click and drag the mouse down to cell A10. Once selected type = and then the criterea. For this I want to base it on Mondays only. So type “Monday” after the the equal sign, and then close with a closing bracket followed by an asterix. See image 3 for how this should look.
Now you have completed that, replicate the same process for the Time Band column, but set your criterea as “0800-0859” and then the Name column but set your criterea to “Smith”. The speech marks are important because it tells Excel exactly what you are looking for. You could also select a cell, so for example instead of typing “Smith” you could just type D3 as this cells contains the name Smith. And then finally select the Income array, but with no criterea and close the SumProduct formula with two )) . This last one has no criterea required because it is summing the income based on the previous three criterea. Remember to put an asterix between the closing and opening bracket of each array. Your final SumProduct should like this:
Hit enter, and Excel provides a total of £7200. SumProduct can be used for so many different sets of data, and I have before used 8 different arrays to get the total I require.