totn Excel

MS Excel 2007: Use an array formula to sum all of the order values for a given client

This Excel tutorial explains how to use an array formula to sum all of the order values for a given client in Excel 2007 (with screenshots and step-by-step instructions).

Question: Is there a way in Microsoft Excel 2007 to VLOOKUP all matches of a number in a column, from another sheet, and return the sum of those matches, from another column on the other sheet? But, if there is only 1 match in the column, return the single value of another column; or if there is no match to the original value in the column, return a "0"?

I've included an example scenario, consisting of 2 separate sheets in the same workbook, both sorted by Client ID. The Total Spend for Product A column is the one that I am trying to populate with the formula. Can you help?

Sheet1 - called "Master Client Spend" with data as follows:

Client ID Client Name Total Spend for Product A
020003 Client A (pull sum from other sheet=$100.00)
020005 Client B (pull sum from other sheet=$2131.00)
020006 Client C (pull sum from other sheet=$1221.00)
020007 Client D (pull sum from other sheet=$0.00)

Sheet2 - called "Data-Product A" with data as follows:

Client ID Client Name Product A Orders
020003 Client A $100.00
020005 Client B $133.00
020005 Client B $999.00
020005 Client B $999.00
020006 Client C $1,077.00
020006 Client C $144.00
020007 Client D $0.00

Answer: This solution does not use the VLOOKUP function, but rather an array formula.

Let's look at the example.

Microsoft Excel

In the second sheet called "Data-Product A, is all of the raw data that we want to sum for each client.

Microsoft Excel

In the first sheet called "Master Client Spend" in column C, we want to sum the total for each client. We've done this with an array formula.

In cell C2, we've created the following array formula that uses the SUM function:

=SUM(('Data-Product A'!$C$2:$C$8)*('Data-Product A'!$A$2:$A$8='Master Client Spend'!A2))

When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows:

{=SUM(('Data-Product A'!$C$2:$C$8)*('Data-Product A'!$A$2:$A$8='Master Client Spend'!A2))}

As you can see, the array formula in cell C2 has returned $100 which is the sum of all orders for that client. This formula is then copied down to cell C3, C4, C5 and so on.