Excel named range with multiple criteria

  GlasgowMary 15:30 17 Sep 2005

I have a database where rows will be added at regular intervals. I have created a dynamic named range, called Data. How do I change my functions to reflect the changes in the database. Here is the function which I am now using.


VoG kindly helped me with the above. I've scoured the net with no success.


  VoG II 15:48 17 Sep 2005

This is an example using the earlier 2-column problem


Note the use of the -- instead of a * here (just to confuse you) but either -- or * should work.

  GlasgowMary 16:31 17 Sep 2005

Sorry, but I don't understand how the function works. Does the -- (or *)refer to the dynamic named range that I've located in another worksheet? Also, do the mydates and myproduct syntax refer to the column names?

As you've probably gathered I am very confused.

Still learning,

  VoG II 16:41 17 Sep 2005



In my mock-up I have defined mydates as A1:A20 and myproducts as B1:B20 (even though my sheet only has data in rows 1:10). The formula returns the correct result.

Compare with my original formula which was


As for using -- or * this is referred to as coercion which is used to force Excel to return a numeric answer. -- is supposed to be more efficient than * but clearly is potentially confusing!

See the following (post by Aladin Akyurek) for an explanation click here

  GlasgowMary 17:33 18 Sep 2005

Thanks so much. I have finished my project. Couldn't have done it without your help - yet again!

Best regards,

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

iPhone X review

How to find a font: Discover the name of a typeface with these apps

The best iPhone for 2017

Comment créer un compte PayPal pour payer en ligne ?