Excel COUNTIF Column D year equals 2015 and Column L equals 15 or greater

Question

I can determine how many cells in Column D with the year 2015 there are. And I can count the number of cells in Column L that are 15 or larger. I want to merge these two formulas, but I'm not sure how to do it.

The formula I use to count the years is:

=SUMPRODUCT(--(TEXT('Sheet1'!D:D,"YYYY")="2015"))

The formula for counting 15 or greater is:

=COUNTIF('Sheet1'!L:L,">=15")

Can someone please provide a way I can combine these two formulas so I can count how many instances there was 15 or greater for the year 2015?

Answer 1

COUNTIFS() with an S allows the ability of multiple criteria and you can bracket the dates:

=COUNTIFS('Sheet1'!L:L,">=15",'Sheet1'!D:D, ">=" & DATE(2015,1,1),'Sheet1'!D:D, "<=" & DATE(2015,12,31))

---

If your dates are actually text strings that look like dates you can do the:

=COUNTIFS('Sheet1'!L:L,">=15",'Sheet1'!D:D,"*2015*")