question

02539402 avatar image
0 Votes"
02539402 asked AAA answered

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

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

7142-%E6%88%AA%E5%B1%8F2020-04-07163457.png
7191-%E6%88%AA%E5%B1%8F2020-04-07163444.png

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)


not-supported
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RebecaLopes-8287 avatar image
0 Votes"
RebecaLopes-8287 answered

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!

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DSPatrick avatar image
0 Votes"
DSPatrick answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AAA avatar image
0 Votes"
AAA answered

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,

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.