Results 1 to 4 of 4
  1. #1
    Star Lounger
    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

  2. #2
    Plutonium Lounger
    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.

  3. #3
    Star Lounger
    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 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

  4. #4
    Platinum Lounger
    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 control-shift-enter).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •