Need Formula (using Lookup, maybe?) (Excel 97)

Topic

New Reply

I need a formula in Excel, and I don’t have the least idea of how to go about it (no surprise there). On the first sheet, I have a list of items in Column A. Across the top, in Row 1, are a bunch of features associated with the items. In the intersecting cells are X’s indicating whether the feature listed in that column goes with the item in that row. On the second worksheet, in Column A, are the same features from Row 1 on the first sheet. In Column B are the prices of the items in Column A. Confused yet?

Okay, here is the problem. On sheet one, in the last column after all the X’s, I want the sum of the corresponding amounts for each item checked in that row. Something like this: if there is an X in Column E in this row, add the amount from Worksheet 2, Row 4, Column B, etc., for all the columns in the range holding X’s. In other words, I need to sum the row, replacing the X’s with the amounts from the other sheet, without actually replacing them.

Thanks for any suggestions. You guys always seem able to figure this stuff out, and it makes perfect sense once I see it done, but I just can’t ever seem to get there by myself.

–Karyl Monaco
Master Instructor – MS Office 2000

Reply | Quote

Replies

If you can live with using 1’s in the cells to indicate existing features instead of X’s, you can use relatively simple array formulas involving SUMPRODUCT and TRANSPOSE. See attached silly example.

Reply | Quote

Thank you so much for the solutions, and sorry for not getting back to you sooner. I was able to get Jan’s suggestion to work, and I suspect I would have been able to get Hans’ to work, too, if I would have figured out the Ctrl-Shift-Enter thing sooner. I knew I could count on you guys! I have the solution to my current problem working well and the techniques socked away for future reference. I’m sure it will come in handy.

Again, many thanks!

–Karyl

Reply | Quote

I did it like this.

Assume the x’s are as in the example below:

	A	B	C	D	E
1		Apples	Pears	Cherries
2	1	x			3
3	2		x	x	9
4	3	x		x	8
5	4

###EDITED JKP, Typo: ROW 2 should be ROW 1

and the prices are on sheet2 as follows (ROW 1 INTENTIONALLY LEFT BLANK!!!):

	A	B
1
2	Apples	3
3	Pears	4
4	Cherries	5

Then use this array formula in cell E2 of sheet1:

=SUM(N(OFFSET(Sheet2!$B$1,IF(B2:D2=”x”,COLUMN(B2:D2)-1,0),0,1,1)))
(confirm with control-shift-enter).

Reply | Quote