Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Re help

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks again Adam, but what if the columns are every other or more?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,312
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey again,

    You know I was afraid you'd ask that

    Actually I guess you could go with adding a series of sumproducts. So if your columns alternate color and price for 5 columns A-J you'd have this ugly beast:

    =SUMPRODUCT((A1:A100="Red")*(B1:B100<50000))+SUMPRODUCT((C1:C100="Red")*(D1:D100<50000))+SUMPRODUCT((E1:E100="Red")*(F1:F100<50000))+SUMPRODUCT((G1:G100="Red")*(H1:H100<50000))+SUMPRODUCT((I1:I100="Red")*(J1:J100<50000))

    Hopefully a guru will post something better than my brute-force approach.

    Adam

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,074
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    Try:

    =SUMPRODUCT((A1:I100="Red")*(B1:J100<50000))

    Note that the ranges shift: A to I for color and B to J for price.

    Aladin

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That's quite a formula. Well, thanks again for your help. I'll see what I can figure out based on what you've given me (which is an incredible help). Oh and just so I don't look rediculas, I'm not comparing cars. It's much more involved than that, which is why I'm having so much trouble. But anyway, you've been a great help!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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