Finding the year of today's date in a Countifs Statement

I am trying to count records only if the referral month is 1 and the referral_start date iis within the current year.

I have name fields as follows   referral month      -      referral
                                                   referral_start_date -  start_date

I am getting a syntax error when I try to add the year comparison.  This is my statement . Can someone tell me what it should be?

=COUNTIFS(referral,1,year(start_date),year(TODAY()))
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?
 
barry houdiniCommented:
You can't apply a function like YEAR to any range in COUNTIFS so you either need to add an extra column with the year and use

=COUNTIFS(referral,1,extra_col,year(TODAY()))

or without extra column do this

=COUNTIFS(referral,1,start_date,">="&DATE(YEAR(TODAY()),1,1),start_date,"<"&DATE(YEAR(TODAY())+1,1,1))

i.e. test whether start date is greater than (or equal to) 1st jan in current year and less than 1st Jan next year

regards, barry
0
 
barry houdiniCommented:
....or you could use SUMPRODUCT with the YEAR function but that's less efficient in general, i.e.

=SUMPRODUCT((referral=1)*(year(start_date)=year(TODAY())))

regards, barry
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.