Excel named range with multiple criteria

  GlasgowMary 15:30 17 Sep 2005
Locked

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.

=SUMPRODUCT(((MONTH(Database!$A$2:$A$200)=5)*(Database!$W$2:$W$200=C$27))*(Database!$Z$2:$Z$200))

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

Thanks,
Mary

  VoG II 15:48 17 Sep 2005

This is an example using the earlier 2-column problem

=SUMPRODUCT(--(MONTH(mydates)=9),--(myproducts="X"))

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,
Mary

  VoG II 16:41 17 Sep 2005

Sorry

=SUMPRODUCT((MONTH(mydates)=9)*(myproducts="X"))

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

=SUMPRODUCT((MONTH($A$1:$A$10)=9)*($B$1:$B$10="X"))

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,
Mary

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

Elsewhere on IDG sites

OnePlus 5 review

See the work of famous artists playing with toys

iPad Pro 10.5in (2017) review

Comment faire une capture d’écran sur un Mac ?