Excel Countif - Need no. of cells in a row that have a date greater than specific date

Question

I have a table like this, where I want to insert formulas in column B to arrive at the indicated values.

The logic is this - I want to count every alternate cell in that particular row, starting from column C till column AA, and get the number of cells that contain a date value greater than or equal to Target date.

Cols/Rows      A        B     C      D     E         F      G
  1       Target Date   X   X Date   Y    Y Date     Z    Z Date
  2       13-12-2015    2               13-12-2015       13-01-2016
  3       24-11-2015    1               25-11-2015       20-10-2015
  4       23-01-2016    0                   
  5       30-01-2016    0               06-06-2016       14-04-2015

To begin with, before I put the condition on the date, I first tried to get the number of alternate columns in this range by using the array formula =IF(MOD(COLUMN($C4:$AA4),2)=0,COLUMNS($C4:$AA4))

But this returns FALSE for some reason. Only if this returns a numeric value, I can proceed with adding a condition for dates.

How do I modify the formula? Any help is appreciated!

Answer 1

You want to use SUMPRODUCT():

=SUMPRODUCT((MOD(COLUMN($C4:$AA4),2)=0)*($C4:$AA4>=DATEVALUE"13/1/2015")*($C4:$AA4<=DATEVALUE"13/1/2016"))

This will return a count of every other column that has a date between 13/1/2015 and 13/1/2016