Results 1 to 4 of 4

20030409, 06:24 #1
 Join Date
 Apr 2001
 Location
 Washington, Washington, USA
 Posts
 57
 Thanks
 0
 Thanked 0 Times in 0 Posts
Need Formula (using Lookup, maybe?) (Excel 97)
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

20030409, 06:52 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Need Formula (using Lookup, maybe?) (Excel 97)
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.

20030411, 04:43 #3
 Join Date
 Apr 2001
 Location
 Washington, Washington, USA
 Posts
 57
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Need Formula (using Lookup, maybe?) (Excel 97)
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 CtrlShiftEnter 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

20030411, 06:22 #4
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Need Formula (using Lookup, maybe?) (Excel 97)
I did it like this.
Assume the x's are as in the example below:
<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center valign=bottom>1</td><td align=right valign=bottom></td><td valign=bottom>Apples</td><td valign=bottom>Pears</td><td valign=bottom>Cherries</td><td align=right valign=bottom></td><td align=center valign=bottom>2</td><td align=right valign=bottom>1</td><td valign=bottom>x</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom>3</td><td align=center valign=bottom>3</td><td align=right valign=bottom>2</td><td align=right valign=bottom></td><td valign=bottom>x</td><td valign=bottom>x</td><td align=right valign=bottom>9</td><td align=center valign=bottom>4</td><td align=right valign=bottom>3</td><td valign=bottom>x</td><td align=right valign=bottom></td><td valign=bottom>x</td><td align=right valign=bottom>8</td><td align=center valign=bottom>5</td><td align=right valign=bottom>4</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td></table>
###EDITED JKP, Typo: ROW 2 should be ROW 1
and the prices are on sheet2 as follows (ROW <font color=red>1</font color=red> INTENTIONALLY LEFT BLANK!!!):
<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center valign=bottom>1</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>2</td><td valign=bottom>Apples</td><td align=right valign=bottom>3</td><td align=center valign=bottom>3</td><td valign=bottom>Pears</td><td align=right valign=bottom>4</td><td align=center valign=bottom>4</td><td valign=bottom>Cherries</td><td align=right valign=bottom>5</td></table>
Then use this array formula in cell E2 of sheet1:
=SUM(N(OFFSET(Sheet2!$B$1,IF(B22="x",COLUMN(B22)1,0),0,1,1)))
(confirm with controlshiftenter).Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association