The following works perfectly when I have numbers as shown below.
=SUMPRODUCT(MAX((A1:A5="Apple")*(B1:B5="California")*(C1:C5)))
A B C
Apple California $10
Orange California $5
Apple Florida $28
Orange Texas $5
Apple California $22
What if I have character instead of numbers. How would I do it?
as shown:
A B C
Apple California good
Orange California ok
madarine Florida Excellen
red Apple Texas expensive
green Apple California very expensive
if green apple and california were searched, then the result will be very
expensive. I don't want to sort these to use vlookup nor I want to use VB.
Is there a way?
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)
Adjust the range to match--but you can only use the whole column in xl2007.
This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.
And you can add more conditions by just adding more stuff to that product
portion of the formula:
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.