Using Countifs to extract a year from an Excel Table

[PolarSpartan]

I have data listed in a Table (meaning the excel function "table", not just cells) and I'm trying to create a summary table from that data. I'd prefer to use formulas for this rather than a pivot table. I'm having a problem with the formula.

One of the countif criteria that I need is the year, but the data includes a full date. If the data was listed in cells (not in a table), the formula "=year()" would extract the year for my countif formula to function, but since it is listed in a table, excel is not recognizing "year()" as a formula -> =countifs(year(Table1[Entry Date]), 2016).

Any suggestions for getting the year extracted for a countif formula without creating a helper column?

 

[NARAYANK991]

Hi ,

I am not sure I have understood your requirement , but you cannot use the construct you have posted.

What you can try is :

=COUNTIFS(Table1[Entry Date] , ">=01-01-2016" , Table1[Entry Date] , "<=31-12-2016")

Modify the date formats to suit.

Narayan

 

[Chihiro]

Same concept as Narayan's. But will work regardless of region setting.
=COUNTIFS(Table1[Entry Date],">="&DATE(2016,1,1),Table1[Entry Date],"<="&DATE(2016,12,31))

 

[PolarSpartan]

Narayan and Chirhiro, thanks for the solutions, that approach will work well.

 

[Khalid NGO]

Hi,

If the data was listed in cells (not in a table), the formula "=year()" would extract the year

Not quite

COUNTIF(s) requires a range weather it is normal range or a Table.

We can not wrap list of dates with YEAR function within COUNTIF due to nature of COUNTIF. However SUMPRODUCT allows this:

=SUMPRODUCT(SIGN(YEAR(Table1[Entry Date])=2016))

Regards,