Sumproduct function...or not?

QuestionEdit

QUESTION: I created a vlookup formula to find and match data from two worksheets. The last step would be for me to multiply the dollar amount (sheet1, column M5:M1267) x quantity (sheet2, column D16:D1278) to get a total onto the third sheet that I created which I want to place the results in column E17:E1279. I'm not sure whether to use sumproduct or another function that would give me this equation.

I have this formula so far but it's not correct and I know this. =SUMPRODUCT('Main List'!M5:M1267,Order!D16:D1278,E17:E1279)

If you can help me understand what data goes into these arrays that would clarify things a lot. Also when it says the dimension must be the same what does that mean?

Thank you! jeannielobue at yahoo dot com

ANSWER: Jeanie,

your ranges

M5:M1267 D16:D1278 E17:E1279

all contain 1263 cells. That is what it means by the dimension must be the same. Each argument in sumproduct must contain the same number of cells in the same shape. You cells are all a single column wide by 1263 cells deep. So you could use them as arguments to sumproduct.

That said, I don't think sumproduct is what you want. As I understood it, you want

Sheet3!E17 to have the formula ='Main List'!M5*Order!D16

then select cell E17 and drag fill it down for 1263 cells (to row 1279) (or copy it and paste to E18:E1279.

then you will get the 1263 different results for Dollar amount times quantity.

If you only wanted the sum of those 1263 multiplications, then you would use sumproduct in a single cell (so that would be like the total cost or something like that).

=SUMPRODUCT('Main List'!M5:M1267,Order!D16:D1278)

but this would go in a single cell because it produces a single number - the sum of the multiplication of each of the dollar amounts against the corresponding quantity.

but I don't think that is what you want. I think you want the individual multiplication results in E17:E1279 which is what the first formula I showed will do.

---------- FOLLOW-UP ----------

QUESTION: I tried the formula as =sum('Main List'!M5*Order!D16) but I keep getting a syntax error. Also tried =sumproduct('Main List'!M5*Order!D16) As well with no luck. If I'm trying to multiply two rows together I don't understand why simple equation won't work.

ANSWER: Jeannie, I did what I said:

Sheet3!E17 has the formula ='Main List'!M5*Order!D16

and it worked fine for me. (see attached image - look at the formula bar to see the formula in E17)) I can't explain why you are having problems, but based on what you described, it shouldn't be the formula. I don't know why you used sum - it isn't required.

Best I can offer is to look at your workbook. Send it to twogilvy@msn.com if you want me to look at it.

---------- FOLLOW-UP ----------

QUESTION: The issue is that because I created a vlookup to match columns from sheet1 & sheet 2, the results displayed aren't sequential but based on the results that match. For example, the first item that turned up a match from the main list (sheet1) was from row 8, the next match is from row 1099 and so on. The list is made up on 1267 rows. Sheet2 is the data being matched and therefore results are reported on sheet3 as shown below.

Item #'sItem Description Rebate Amount Total Amount

1. N/A #N/A #N/A

1518 CHX WING DRM 1ST CK BRD 4403 TY $4.00

1. N/A #N/A #N/A

8186 HAM CURE 81 WHOLE 27992 HORM 8# $0.03 8473 BACON LO APWD 13/17 32669 HOR20# $0.03

1. N/A #N/A #N/A
2. N/A #N/A #N/A
3. N/A #N/A #N/A

20670 FRENCH BRDSTICK RICH87783 F 1.5Z $0.50

1. N/A #N/A #N/A
2. N/A #N/A #N/A
3. N/A #N/A #N/A

60221 FILM PVC STD ZSAF 30510474 18X2M $1.63

1. N/A #N/A #N/A

AnswerEdit

Jeannie,

I don't see a question here. I set up your workbook and returned it as soon as I finished teaching my 5 hour class. It pretty much shows what you show, but you must have used different quantities for an item or two as a couple of our numbers differ.

Based on this post, either you solved your problem or what I sent you shows you how to get what you show here.