COUNTIFS with DATE

Kirstine

Another day, another question to the Community...!

I'm trying to get a count of all jobs starting in each month. My formula is giving me an #INVALID OPERATION error though.

=COUNTIFS({Start Date} >= (DATE(2019, 1, 1)), {Start Date}, <=(DATE(2019, 1, 31)))

What am I doing wrong?

翟边

A missing,after the first {Start Date} range?

>=DATE(2019,1,1) is sufficient. There is no need for additional brackets.

Kirstine

Yaaaaaaaaaaas!!!! Thank you! What a plonker! Ha ha! (I was grasping at straws with the extra brackets!)

Thanks for the sanity check!

Paul Newcome

As a suggestion... There are a couple of ways to double up on the accuracy to make sure nothing gets missed.

1. Instead of referencing specific dates, reference the month (and year if needed) specifically.

>= DATE(2019, 1, 1) and <= DATE(2019, 1, 31)

can change to

MONTH(@cell)= 1

and to reference the year

YEAR(@cell)= 2019

.

2. If you wanted to use specific dates without having to worry about whether there are 28, 29, 30, or 31 days in the month, you can reference the first of next month and subtract 1.

<= DATE(2019, 1, 31)

can change to

<= DATE(2019, 2, 1) - 1

.

Both of these methods will ensure that you are covering the entire month without having to worry about a specific number of days.

.

A tip for consolidation and organization:

If you are referencing the same range more than once within a COUNTIFS (or even a SUMIFS for that matter), you can use the AND function along with@cellreferences to combine the criteria.

I personally find this helpful in keeping track of everything that is happening in the more complex formulas.

You can also leverage this to work an OR function into the formula if you needed that as an option as well.

=COUNTIFS({Date}, >= DATE(2019, 1, 1), {DATE}, <= DATE(2019, 1, 31))

can be changed to

=COUNTIFS({Date}, AND(@cell>= DATE(2019, 1, 1),@cell<= DATE(2019, 1, 31)))

or

=COUNTIFS({Date}, AND(@cell>= DATE(2019, 1, 1),@cell<= DATE(2019, 2, 1) - 1))

or

=COUNTIFS({Date}, AND(MONTH(@cell)= 1, YEAR(@cell)= 2019)

Kirstine

Thanks Paul, this is what I was wanting. I'm using=COUNTIFS ({Start Date}, AND (MONTH(@cell)= 1, YEAR (@cell)= 2019), but am now getting #INVALID DATA TYPE.

Paul Newcome

That is because there is most likely at least one cell within the range that is not a date (either blank, text, or number). To account for this, we will work in an IFERROR statement into both the MONTH and YEAR functions to say that if there is a cell without a date in it, use 0 (zero) for the month and year respectively. We use zero because there is no month or year number zero, so it won't inadvertently get counted.

=COUNTIFS ({Start Date}, AND(IFERROR(MONTH(@cell), 0)= 1,IFERROR(YEAR(@cell), 0)= 2019)

Kirstine

Ah OK, yeah the sheet I'm referencing does have blanks!

Thanks very much, this now works perfectly. I just need to go through it all changing the month...!

Paul Newcome

Just a quick recommendation...

If you are displaying the counts in a metrics table like this...

January

February

March

April

or like this...

January February March April

.

You can create a helper column or row and reference specific cells containing the month numbers. This will allow dragfilling and keep you from having to edit the formula itself. You can also use a cell reference for the year.

I usually have mine set up like so...

1 2 3 4

2019 Jan Feb Mar Apr

Af

B

C

.

Where the first column is let's just say [Service Type] and the rest of the columns are the same as what is in row two (Jan, Feb, etc.), I would enter the following where you seefand then dragfill both down and over.

=COUNTIFS({Date}, AND(IFERROR(MONTH(@cell), 0) = Jan$1, IFERROR(YEAR(@cell), 0) = $[Service Type]$1), {Service},@cell= $[Service Type]@row)

.

Note the use of the dollar sign $. This locks in either the column or row reference (whichever comes after it) and let's you essentially override the auto-update feature when dragfilling so that specific column, row, or cell references stay how they need to.

.

If you were using the first setup, it could look something like this (column names in bold):

Number Month Count

2019

1 Janf

2 Feb

3 Mar

4 Apr

.......

and the formula could look something like this...

=COUNTIFS({Date}, AND(IFERROR(MONTH(@cell), 0) = Number@row, IFERROR(YEAR(@cell), 0) = Number$1))

.

You could then dragfill down the Count column and hide the Number column to keep the sheet looking clean.

Just another tip for consistency and accuracy of data.