I hope the picture tells it all: I’ve prepared simple array and just want to count occurrences of number “2”, but I can’t figure out why I get error as a result.
Am I missing something?
As mentioned by @PYS, you are using the database function incorrectly, and should consult the relevant help page (also referenced in that answer) to see how it is expected to work.
Specifically:
- Any “database” range (the first argument in DCOUNT) is expected to have field names in its first row. So when you define the range
A2:A6as database, you have 4 records having a single field each, named “1”. This is likely not what you wanted, and probably you wanted to extent the range to beA1:A6, whereA1would be something like “my_values” (I will continue with this assumption below). - The “search criteria” range must reference at least two rows, where first row is again for field names, and following rows are for required values or conditions. E.g., you could put “my_values” to
B1, and 3 toB2, and then useB1:B2as your search criteria to get what you want.
The formula then would be:
=DCOUNT(A1:A6;;B1:B2)
Note that in this case, you also may count how many occurrences of 1 are there, because I assumed that A2 is part of the data, not the field name. If your original database range were used, then I needed to put “1” to B1 (the formula would become =DCOUNT(A2:A6;;B1:B2)), and then the search for 1 put to B2 would result in 0.
Mike, thank you very much. I think the problem was in the “search criteria”, which should be entered as range, not a single cell.