Re help

Autumn · Apr 25th, 2002, 01:03 PM

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

Asala42 · Apr 25th, 2002, 01:22 PM

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

Aladin Akyurek · Apr 25th, 2002, 01:40 PM

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

Autumn · Apr 25th, 2002, 01:56 PM

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!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 25th, 2002, 01:03 PM	#1
Autumn New Member Join Date: Apr 2002 Posts: 8	Thanks again Adam, but what if the columns are every other or more?

Apr 25th, 2002, 01:22 PM	#2
Asala42 Board Regular Join Date: Feb 2002 Location: Southfield,MI USA Posts: 1,029	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

Apr 25th, 2002, 01:40 PM	#3
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	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

Apr 25th, 2002, 01:56 PM	#4
Autumn New Member Join Date: Apr 2002 Posts: 8	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!