How does DCOUNT function in Excel work when the criteria is a simple string?

Question

This is a spreadsheet example, where the first one shows the formulas, while the second one shows the calculation result of formulas.

Regarding the calculation result of cell A12, why is it 3 instead of 1, since the criteria shown in cell A3 is a simple string A instead of A*? Documentations about DCOUNT function cannot explain this explicitly, so I can only ask for help here. Thanks!

P.S. My Excel version: Microsoft Excel for Mac 16.35 (20030802）

Answer 1

If you want to count only exact values, you had better use COUNTIF function. To do it, use: COUNTIF(A5:A10,$A$3).

Good luck!

Answer 2

QnA currently supports the products listed in right-hand pane (more to be added) Better to reach out to subject matter experts in dedicated forums over here.

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_mac

(please don't forget to mark helpful replies as answer)

---

Regards, Dave Patrick ....
Microsoft Certified Professional
Microsoft MVP [Windows Server] Datacenter Management

Disclaimer: This posting is provided "AS IS" with no warranties or guarantees, and confers no rights.

Answer 3

Very good question.

An undocumented feature in Microsoft Excel

The official help page in Excel in

https://support.microsoft.com/en-us/office/dcount-function-c1fc7b93-fb0d-4d8d-97db-8d5f076eaeb1?ui=en-us&rs=en-us&ad=us

indicates this under the criteria for the field Tree in the example

="=Apple"

If you enter in cell A3 the formula ="=A", visible as =A after the formula is entered, you will get the correct results
And if you enter ="=A*" you get the same result as just entering the value A.

That means any text is interpreted as ="=<text>*"

As a wildcard you can also list the values with A and any -but only one- character by using ="=A?"
Or anything ending in A, ="=*A" .

The second = is important. If missed there is no error, just incorrect results. Try ="*A"

Regards,