Countifs date is this month and a day of the month

Sam Swain

Hello all,

I am using the formula to count from a list of dates in another sheet to find all entries that are from this month.

=COUNTIFS({Date}, IFERROR(MONTH(@cell ), 0) = MONTH(TODAY()), {Bay}, Bay@row)

I would like it to also register if it is the 1st of the month (for the column named 1st) and the 2nd of the month (for column named 2nd) etc.

Please help!

K. Bátor Nagy

Hi Sam,

I hope you are doing well!
As far as I know, there is no way to target the column name as a value for picking the day.

However, there is a solution to your question. All you need is adjust all the formulas in all the columns so that they are looking at the correct day. You can do it either by replacing the "X" in the below formula with the number that matches the column name ("1" for "1st", "2" for "2nd", etc.) OR you can create sheet summary fields and reference those as values (though this is more tedious). It is up to you.
Here is your formula that includes the day info as well:

=COUNTIFS({Date}, IFERROR(MONTH(@cell ), 0) = MONTH(TODAY()), {Date}, IFERROR(DAY(@cell ), 0) = X, {Bay}, $Bay@row)@cell)

Please see a screenshot below of what it looks like in action.

Answer for days.png

I hope it helps! :)

Bátor Nagy
MASA Consult
Web: www.masaconsult.de
E-mail: bator.nagy@masaconsult.de

K. Bátor Nagy

Hi Sam,

I hope you are doing well!
As far as I know, there is no way to target the column name as a value for picking the day.

However, there is a solution to your question. All you need is adjust all the formulas in all the columns so that they are looking at the correct day. You can do it either by replacing the "X" in the below formula with the number that matches the column name ("1" for "1st", "2" for "2nd", etc.) OR you can create sheet summary fields and reference those as values (though this is more tedious). It is up to you.
Here is your formula that includes the day info as well:

=COUNTIFS({Date}, IFERROR(MONTH(@cell ), 0) = MONTH(TODAY()), {Date}, IFERROR(DAY(@cell ), 0) = X, {Bay}, $Bay@row)@cell)

Please see a screenshot below of what it looks like in action.

Answer for days.png

I hope it helps! :)

Bátor Nagy
MASA Consult
Web: www.masaconsult.de
E-mail: bator.nagy@masaconsult.de

Sam Swain

Hi Bátor! Thanks for your response.

I'm getting an UNPARSEABLE error for some reason? I've put in the below formula

=COUNTIFS({Date}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), {Date}, IFERROR(DAY(@cell), 0) = 1, {Bay}, $Bay@row)@cell)

Is that correct?

Sam Swain

I've just deleted the @cell) at the end of the formula and now it is working. Do you see an issue with the count if I don't have an @cell function at the end?

K. Bátor Nagy

Hi Sam,

I have just realized that I have added an extra "@cell)" when I copied the formula here. It is not part of the formula, just a mistake.
Good catch!

I am happy that it works for you now!

Bátor