THANK YOU BRIAN!!!!
I was beginning to wonder if I would get any feedback on this at all.
I apologize in advance for the length of this post, but you ask very fair questions which should have some detailed responses.
This case was actually presented to me in a different (customer provided) workbook, as an example of their "claim" that formula based criteria is broken in Excel 2007 for the Advanced Filter.
I built the posted reproduction to demonstrate their usage of the DCOUNTA function because, in their workbook, they were using this exact criteria method to find known matches in the data field, which the Advanced Filter is missing when using the same exact criteria as the DCOUNTA formula.
Indeed, the customer's workbook was sent to me with the Advanced Filter applied (finding no matches), but the DCOUNTA formula correctly showing 2 matches which should be shown by the filter.
It is VERY HARD to get an Excel Bug past me.
While I have documented several, they represent a VERY SMALL percentage of the cases presented to me.
This one being no exception, I picked it apart, practically cell by cell.
Of course, I quickly noticed the non-standard method of building the criteria (when compared to the documented methods in various Excel help files and books).
I also noticed the fact that their usage of the criteria actually FAILS and allows the Advanced Filter to work when documented methods are used.
However, I also noticed the oddity which I presented in this forum with regard to the criteria heading. This oddity is what actually seems to allow their usage of the criteria to work in the DCOUNTA function.
Initially, I simply fixed it according to conventional criteria methods described in numerous Excel Help files and books, provided some "guidance" on why it was not working before, and sent them a fixed workbook.
A few days later the presenting agent forwarded me a translated copy (the customer is French) of an email from the customer. It was a scathing attack about how I know little of Excel, I never looked at the workbook, and how all I had done is revert the criteria to literal values instead of the formula based criteria they presented.
In reality, I suspect the opposite is actually true (they being the ones who did not bother to study my repaired workbook).
In my repaired workbook, I had no literal values, it was all formula based, but in the criteria cell, it showed an actual value ("=52.0000456245") instead of "FALSE" as it was shown originally (and in the posted reproduction).
I have yet to reply to them.
For reasons which should be readily apparent to any members of this forum who have read my posts, I need to keep an open mind about what I see presented to me.
So I posted here to see if anybody could shed some light on their usage of which I am unaware.
To answer your question directly, their workbook clearly wants to use an Advanced Filter with a formula as the criteria, but since it is not working, they have used the DCOUNTA function as a means to show missed matches actually do exist in the data field. However, I believe they have not presented to me their actual workbook usage of the Advanced Filter so I'm not entirely sure why the formula in the criteria cell has to be built the way they are using it.
“Solve it”???
Well, some customers are very dead set in their methods.
I have shown them one method of using a formula as the criteria which allows both the Advanced Filter and the DCOUNTA function to work properly.
Still they insist their usage is correct and my solution does not meet their needs.
So naturally this is where I came.
I really figured that either you, or boblarson, or one of the many other masters here would soon enough show me the errors of my ways. Since the customer was so adamant, I fully expected to be told the customer is correct.
However, at some point, the deafening silence which came as a result began to speak volumes.
My thinking being that if after the amount of time this has been posted, the masters here looked at it and have not chimed in with support for the customer’s usage as being within specification or an otherwise a known usage in previous versions of Excel (besides 2007), then perhaps there is no correct support for it.
I am busy for most of the rest of the day, but later today, or tomorrow, I will build a full reproduction of their workbook, including an applied Advanced Filter on one sheet as well as what I believe should be a “correct” usage on another sheet.