-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Re help
- Thread starter Autumn
- Start date
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
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
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
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
=SUMPRODUCT((A1:I100="Red")*(B1:J100<50000))
Note that the ranges shift: A to I for color and B to J for price.
Aladin
Upvote
0
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!
Upvote
0
