Question about DCOUNTA in Excel 2007

Bilbo_Baggins_Esq

Registered User.
Local time
Today, 16:01
Joined
Jul 5, 2007
Messages
586
I have attached a workbook to reproduce a question I have regarding the DCOUNTA function.
On it, you will find:
a database area with three field headings (A3:C17)
3 defined criteria ranges with headings and values (E1:E2, F1:F2, G1:G2)
3 cells with specific DCOUNTA functions (G4:G6)

I would like to know why the DCOUNTA functions in G4 and G6 return O as a result if their criteria range headings (E1 and G1 respectively) are equal to their respective column headings in the database range.
The only way G4 or G6 return accurate results is if E1 or G1 are any value other than the value of the column headings in the database range.

Now I freely admit that the formula contained in the actual criteria cells for E2 and G2 are unusual.
However, they do seem to work, as long as the criteria heading is different than the data heading.

I have included a “normal” usage of the DCOUNTA function in G5 with its associated criteria range being F1:F2.

I’ve not used the DCOUNTA much, I am by no means an Excel novice.
I admit, I don’t get it.
Can anybody explain it to me?
 

Attachments

OK

This is quite old
I've never used DCOUNTA
I don't use 2007 so it has been converted.

BUT I noticed that if you look at the Function argument ie click fx then criteria1 and 2 flag up #Value! error.

Bit puzzled by not only what happens but what you are trying to do, Oh! and did you solve it?

Brian
 
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.
 
I doubt that I am going to provide much if any help, it appears that we have a double selection =zero effect.

The column counted is that referred to in the formula used in the criteria, if however you also select a column, even the same one, in the column heading in the criteria then oops answer is 0.

I wonder if this an unkown/undocumented feature :D

Brian

EDIt

Of course if I had done a bit of reading of Exel help instead of dredging the net I would have found

You can use a calculated value that is the result of a formula as your criterion. When you use a formula to create a criterion, do not use a column label for a criteria label; either keep the criteria label blank or use a label that is not a column label in the list.
:o
It doesn't say what happens mind you.
 
Last edited:
I should have added that that help was not under Dcounta but filter -advanced criteria

Brian
 

Users who are viewing this thread

Back
Top Bottom