0

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!

user3164272
  • 565
  • 1
  • 9
  • 20

3 Answers3

0

I don't have access to Excel at the moment to check, but I suspect the issue is that you're calling COLUMN with arguments. I think if you remove the range, and do =IF(MOD(COLUMN(), 2)=0, COLUMNS($C4:$AA4) the if will at least get true on even columns. COLUMN returns the numeric index of the current column, so you might need to fiddle with things to get this working.

FungalRaincloud
  • 16
  • 2
0

The brute-force approach, write as many of these as you need, =1*(C5>A5) + 1*(E5>A5) + 1*(G5>A5) + 1*(I5>A5) and so on.

VolleyballAddictSandiego
  • 363
  • 2
  • 10
  • 1
    Brute Force would not help me here, since I need to modify the formula each time a new column with data is added. – user3164272 Jan 31 '16 at 19:14
0

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

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Thank you! Supposing I want to have the range as $A4:$AA4, is there any way by which I can exclude only the current cell in which I type the formula, so as to avoid circular reference? – user3164272 Jan 31 '16 at 20:25
  • @user3164272 not that I know, You could just add a second sumproduct to the first to get the cells on the left and right. – Scott Craner Jan 31 '16 at 20:41
  • Thanks, that kind of helps. I shall give it a shot. – user3164272 Jan 31 '16 at 21:07
  • In some cells though, I have text values such as "Due". Those are also getting counted as greater than the specified date. For example, in cell E4, if I type some text, then B4 gives the value as 1 instead of 0. How do I fix this? – user3164272 Jan 31 '16 at 21:14