countif(s) on a named range with function

[prino]

I’ve got a table with dates, and would like to fill in a 12(months, B1:M1) x 31(days, A2:A32) with the number of days for each month/day combination. The column with dates is named (“C_ddays”), so I started by using the “wizard” which resulted in putting “=countif(day(C_dday),=A2)” and that returned “Err:510”.

Next try was “=countif(day(C_dday),”=A2")" (Err:504)
Next try was “”=countif(day(C_dday),"="&A2)" (Err:504)

So what is the way to do it???

Turns out that it works using “sumproduct”. Not very intuitive.

[anon73440385]

Could you provide a sample file?

[Lupp]

Since you are Dutch, I would assume you are using a NL-compatible UI and or locale.
Therefore I would also expect your parameter delimiter is the semicolon.
(I didn’t read through the days and months stuff.)
Anyway, without checking any aspect of the semantics, {=COUNTIF(DAY(C_dday);A2)} should work. The comparator “=” is default. Wanting to makle the comparator explicit, you might also use {=COUNTIF(DAY(C_dday);"=" & A2)} to the same effect.
The formulas must be entered for array-evaluation (Ctrl+Shift+Enter) because the array needed as the first parameter must be created by a function which by default would return a single value.
Do not enter the curly brackets! They only show the fact of array-evaluation, and are created automatically.

(See also: Why do I often get an error (508, 504, 502 e.g.) if I paste a Calc formula from some post into my sheet? )

[prino]

@anon73440385: Just create a sheet with dates from 2020-01-02 to 2020-12-31 in column “O”, name if C-dday and add the rest.

@Lupp: My UI is en_US, locale is en_GB. And neither of the two formulae you gave, replacing the “;” with a “,” works.

Anyway, I found out that using SUMPRODUCT works. Totally counterintiutive.

[Lupp]

The semicolon is accepted as the parameter separator by all the locales, even if they use and show the comma in the place by default.
The formulas I gave you are syntactically correct, and the explanation concerning the array evaluation is well considered. I actually do such things every day. If there is an additional problem related to the named range, I cannot know.
To supply an example sheet demonstrating the issue is your job.
The function name SUMPRODUCT was clearly chosen very badly. It cannot be changed without getting incompatible with Excel. A relevant advantage of using SUMPRODUCTS for tasks of the kind discussed here is that its parameters are specified ‘ForceArray’ for what reason you don’t need to force array-evaluation explicitly.