Hi Mike,
Thanks for your contribution.
I'm certainly interested in your comment... "I'm always a bit nervous of the SUMIF, COUNTIF etc functions performance-wise."
Can you elaborate ? If there's something we all should know about, then it would be GREAT if you would share your concerns. Thanks.
As for "sharing", here's another "offering"... yet another alternative to resolving Keith's task... Again, I've tested the following formula, and it "works like a charm".
=DMIN(data,1,crit)
...where "data" is a Range Name assigned to the database.
...where "crit" is a Range Name assigned to the following criteria...
field1
<>0
While I agree that "array formula" are often very useful, please appreciate that MANY Excel users OVERLOOK the VERY POWERFUL built-in "database functionality" of Excel.
Many Excel users, in fact, do NOT even know that this power EXISTS !!!
Part of the reason for this, is that Excel "Help" unfortunately is not very elaborate in this area. PLUS, there are "quirks" that one has to identify and "work around".
One example... If a user goes to use "Data - Filter - Advanced Filter", and attempts to extract data to another worksheet, the user will encounter an error message saying that it's NOT possible. ...NOT true. Indeed it IS possible.
Here's code that does exactly what Excel says is NOT possible...
Range("data"

.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:="crit", _
CopyToRange:=Range("out"

, _
Unique:=False
It the above example, "data" is on a SEPARATE sheet from the "output range” - named "out".
As an example of what I referred to earlier, about MANY Excel users NOT being aware of this POWERFUL "database functionality"... Please refer to thread68-263488 - where you'll find a GOOD example.
Here was a guy who REALLY appreciated "being introduced" to Excel's "database functionality". And, as you’ll notice if you read the thread, it was really a “shot in the dark” situation where I “dropped the suggestion” that he consider using Excel’s “database functionality”. END RESULT - He was SO impressed, he "dropped" his development of an Access database - in favor of using Excel's database functions - because (he says) in that particular case it made complete sense to do EVERYTHING within Excel - because it's MUCH easier and simpler.
There are, of course, other applications that REQUIRE the use of a TRUE database. However, I hope to convince those MANY Excel users who are still "unaware", that indeed there are MANY applications that can and should be "self-contained" right WITHIN Excel.
I hope you, and others, appreciate the importance of what I've attempted to describe, and will "take a serious look" at Excel's "database functionality".
If anyone runs into difficulty, and/or would appreciate receiving example files, please don't hesitate to ask.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca