# Thread: Sumproduct: Dates by year matched against certain terms, ignore blanks/text

1. ## Sumproduct: Dates by year matched against certain terms, ignore blanks/text

Hello all,

I'm building a tracker for my office and must delineate certain terms in Column A with dates in Column H from this year.

My attempt:
=SUMPRODUCT((A10:A10000="Active")*(H10:H10000=year(today()))

Also:
=COUNTIFS(A10:A10000,"Active",H10:H10000,YEAR(TODAY()))
=INDEX( MATCH("Active",\$A\$10:\$A\$11,0), MATCH(YEAR(TODAY()),\$H\$10:\$H\$11,0))
=SUMPRODUCT((\$A\$10:\$A\$11="Active")*(isdate(\$H\$10:\$H\$11=YEAR(TODAY()))))
=SUMPRODUCT((A10:A10000="Active")*--(H10:H10000<>"")*--(YEAR(H10:H10000)=2017))

I must also avoid text and blanks input in the date column.

Help!

2. Why does the first Sumproduct() not work? It will avoid counting blanks and text entries

3. I don't know. I just get "0," despite verifying that there are, indeed, dates within the range.

4. Are you able to post a sample workbook. So we can. Checked what's going on?

5. See attached.

6. Try this:

=SUMPRODUCT((A10:A10000=\$B\$1)*(D10: D10000>=DATE(YEAR(TODAY()),1,1))*(D10: D10000<=DATE(YEAR(TODAY()),12,31)))

or more efficiently

=COUNTIFS(A10:A10000,\$B\$1,D10: D10000,">="&DATE(YEAR(TODAY()),1,1),D10: D10000,"<="&DATE(YEAR(TODAY()),12,31))

7. Originally Posted by NBVC
Try this:

=SUMPRODUCT((A10:A10000=\$B\$1)*(D10: D10000>=DATE(YEAR(TODAY()),1,1))*(D10: D10000<=DATE(YEAR(TODAY()),12,31)))

or more efficiently

=COUNTIFS(A10:A10000,\$B\$1,D10: D10000,">="&DATE(YEAR(TODAY()),1,1),D10: D10000,"<="&DATE(YEAR(TODAY()),12,31))
It worked! Thank you very much!