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.
In the second sheet called "Data-Product A, is all of the raw data that we want to sum for each client.
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:
=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.
