Excel named range with multiple criteria

  GlasgowMary 15:30 17 Sep 05
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 05

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 05

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 05

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 05

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.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Alex Chinneck’s giant ice cube Christmas tree at Kings Cross

Apple rumours & predictions 2017: The iPhone 8, new iPads, and everything else you should expect fr7…