Range reference and return

[PritiOne]

Hello. Hope all had a wonderful holiday!

I am looking for help on the folowing:

I have 3 defined ranges, say Range 1, Range 2, and Range 3 defined on a sheet in a workbook.
On another sheet "Input Sheet" I have a list input by a user.

I need to know how to write a formula (could be elsewhere in the workbook) so that if the user chooses at lease 1 "product" from Rang 1, and at least one "product" from Range 2, and at least one "product" from Range 3 on the "Input Sheet" a result would follow as "1% discount".

Please let me know if you need any further information & Thanks in advance for the help!!!

 

[PritiOne]

Also,

There are many products in each range...

It worked so well on your example!

 

[fairwinds]

Looks ok.

Did you Ctrl + shift + enter? ( you can see the curly brackets around the formula in the formula bar if done correctly)

 

[PritiOne]

Yes, I saw the curly brackets and he formula reads 0%, but when I go to the sheet where I add products I addes one product from each range and one product not included in any range and the result didn't change... remained 0%.

I entered the formula on the Instructions Sheet, and entered the products on the Input Sheet.. and the products themselves are on a ProductList Sheet.. does that matter?

Thanks!

 

[fairwinds]

No it should not matter.

Try highlighting the different parts of the formula and press F9 to evaluate. Then you might see wich part that fails. Each MAX(...) should return 1 if it is a match in that table.

 

[PritiOne]

Wonderful! Thanks to all for your continued help!!!!!!

 

[Aladin Akyurek]

The formula Fairwinds proposed can be simplified a bit...

=IF(COUNT(MATCH(NewProdList,MedMains,0))*COUNT(MATCH(NewProdList,Connect,0))*COUNT(MATCH(NewProdList,Assessment,0)),1%,0%)

which you still need to confirm with control+shift+enter.

 

[PritiOne]

Hello all...

I am a second stage on this same issue where I have to edit my formula so that if a product from range 1 and product from range 2 and product from range 3 are selected a discount will apply... BUT if a product from range 4 is selected, the discount does not apply... How do I add this to the formula:

=IF(MAX(--ISNUMBER(MATCH(NewProdList,MSDiscMain,0)))*MAX(--ISNUMBER(MATCH(NewProdList,Connect,0)))*MAX(--ISNUMBER(MATCH(NewProdList,Assessment,0))),10%,0%)

 

[fairwinds]

Building on Aladin's simplified formula:

=IF(COUNT(MATCH(NewProdList,MedMains,0))*COUNT(MATCH(NewProdList,Connect,0))*COUNT(MATCH(NewProdList,Assessment,0))*(COUNT(MATCH(NewProdList,RangeFour,0))=0),1%,0%)

 

[PritiOne]

Thanks for the help.. but I am not getting the desired result.

I am using the following formula:

=IF(COUNT(MATCH(NewProdList,MedMains,0))*COUNT(MATCH(NewProdList,Connect,0))*COUNT(MATCH(NewProdList,Assessment,0))*(COUNT(MATCH(NewProdList,NotEligible,0))=0),1%,0%)

Logic:

If at least 1 product from MedMains, AND Connect, AND Assessment are ordered then customer receives a 10% discount.

If at least 1 product from MedMains, AND Connect, AND Assessment AND NotEligible are ordered then customer receives a 0% discount.

I am truing to edit the formula, and am running into difficulties... HELP!!! Thanks in advance for your time!

 

[PritiOne]

I found my error.. Thank you for the formula!

Can I add a second range to search for the same data, so that I can search NewProdList and ExpList for the same data... some "required" products might be in NewProdList OR ExpList.

Thanks again for the help!