# 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!

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.

Qualcomm Snapdragon 835 benchmarks: Antutu, Geekbench 4, GFXBench and PCMark results

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

This stop-frame animation tells a moving story of domestic violence for Refuge

New iPad 2017 preview: Apple's affordable but underspecced new iPad may appeal to the education…