SUMPRODUCT PROBLEM (XL2K SR1)

Topic

New Reply

I’m having a problem with a sumproduct formula where I want to cross multiply two arrays. The first array is a range of six cells. The second is a six element array created with the offset command.

When I select the individual arrays in the formula bar and press F9, I can see all the correct elements displayed, but my result of the sumproduct is zero.

Can someone help out. This seems like it should work.

Thanks,
Ken

Reply | Quote

Replies

I think it’s too complicated, I’d use the column of intermediate results.

Reply | Quote

Try the array (confirm with ctrl-shift-enter)

=SUM((A8:A13)*VLOOKUP(B8:B13,WCTbl,2,0)/100)

Steve

Reply | Quote

Steve,
The formula is taking the result of the first VLOOKUP and using that to calculate against the rest of the rows.

Salary	WC Code	VLOOKUP Value	Vlookup*Sal/100	$0.39*Salary/100
18014.88	8810	0.39	70.26	70.26
46161.44	8832	0.54	249.27	180.03
6995.46	9015	5.32	372.16	27.28
14428.54	8835	4.3	620.43	56.27
30885.92	8835	4.3	1328.09	120.46
11962.91	8810	0.39	46.66	46.66
		Totals:	2686.87	500.95

Reply | Quote

This seems to be a “bug” in calculating the array in XL that I missed when I created it (and seems to be in both XL97 and XL2002 in my subsequent tests and XL2000 based on your comments)

I created the entire 6 row array and built it in steps. When I got to the end I had 6 rows with the “2686” value which means that the array in a single cell should give this value. I did not do confirm this (which is why I missed it).

If you put the array calculation in more than 1 cell, it calculates correctly, but if only in 1 cell, XL seems confused by it and miscalculates. It seems you will have create a 2 row array and hide the 2nd value or use the intermediate calculation route, unless someone else has a workaround to the bug

Steve

Reply | Quote

Thanks to all for the input. Looks like I’ll be using an intermediate column.

Ken

Reply | Quote

Try this one:

=SUMPRODUCT(A8:A13,SUMIF(F7:F30,B8:B13,G7:H30))/100

It’s a normal formula, not an array formula (the array is already implicit in SUMPRODUCT).

Reply | Quote

Great solution, Hans. I didn’t even think to use SUMIF for this. Funny thing is that I was playing around with the problem today, and came across a solution using LOOKUP instead of VLOOKUP or MATCH/INDEX. Like this:

=SUMPRODUCT(A8:A13*LOOKUP(B8:B13,F7:F30,G7:G30))/100

However, I think your solution is preferable because LOOKUP is fussy about the search order and thus is more prone to error if something is not in the proper order.

Thanks,
Ken

Reply | Quote