COUNTIF using structured reference and dates

[PatRichard]

Greetings - I didn't see this in a search, but maybe I'm not searching right. I have a table with a column of dates. Some of the dates are mm/dd/yyyy, and some are just mm/yyyy. I'm trying to count the number of cells in a column in which the year is a specific value. But I'm just coming up with 0. I'm using:

=COUNTIF((MyInventory[Born On]),1985)

but also tried replacing the year with a cell reference (I'm building a table that shows quantities by year).

What am I missing here?

 

[Fluff]

How about

=SUMPRODUCT(--(year(MyInventory[Born On])=1985))

 

[PatRichard]

How about

=SUMPRODUCT(--(year(MyInventory[Born On])=1985))

I get "A value used in the formula is of the wrong data type"

 

[Fluff]

Make sure that all the dates are real dates & not text.

 

[jdellasala]

Greetings - I didn't see this in a search, but maybe I'm not searching right. I have a table with a column of dates. Some of the dates are mm/dd/yyyy, and some are just mm/yyyy. I'm trying to count the number of cells in a column in which the year is a specific value. But I'm just coming up with 0. I'm using:

=COUNTIF((MyInventory[Born On]),1985)

but also tried replacing the year with a cell reference (I'm building a table that shows quantities by year).

What am I missing here?

If you're not getting a valid return from something like COUNTIF (or COUNTIFS if you have a recent version of Excel - what OS and Excel Version can be added to your Profile), then those "dates" are not actually dates. If they were, regardless of their formatting (mm/dd/yyyy or mm/yyyy) they would represent an actual date, although in the second case you wouldn't know what the date is without either changing the format or using the DAY function. You should fix the data rather than find a work-around to bad data.

 

[Automatrix]

Greetings - I didn't see this in a search, but maybe I'm not searching right. I have a table with a column of dates. Some of the dates are mm/dd/yyyy, and some are just mm/yyyy. I'm trying to count the number of cells in a column in which the year is a specific value. But I'm just coming up with 0. I'm using:

=COUNTIF((MyInventory[Born On]),1985)

but also tried replacing the year with a cell reference (I'm building a table that shows quantities by year).

What am I missing here?

Try this, maybe the year is formatted as text.

=COUNTIF((MyInventory[Born On]),"1985")

 

[Fluff]

How is that gong to work when the OP said

Some of the dates are mm/dd/yyyy, and some are just mm/yyyy

 

[jdellasala]

How is that gong to work when the OP said

That doesn't determine whether or not they're dates. They could be actual numeric date values badly formatted. If not, they should be fixed.

 

[Fluff]

I am fully aware of that, see post#4
And your comment makes no sense in relation to what I said in reply to post#6

 

[Automatrix]

Are you able to add this column to MyInventory, and then count that instead?

=YEAR([@[Born On]])