3
+------+--------------+--------------+--------------+--------------+--------------+
|      |      A       |      B       |      C       |      D       |      E       |
+------+--------------+--------------+--------------+--------------+--------------+
|   1  |  7/05/2014   |  7/06/2014   |  7/07/2014   |  7/08/2014   |  7/09/2014   |
+------+--------------+--------------+--------------+--------------+--------------+
|   2  |      YES     |     NO       |       NO     |     YES      |      YES     |
+------+--------------+--------------+--------------+--------------+--------------+
|   3  | Yes to date: |      1       |              |              |              |
+------+--------------+--------------+--------------+--------------+--------------+
|   4  | No  to date: |      2       |              |              |              |
+------+--------------+--------------+--------------+--------------+--------------+

Let's say I have a table like above. I want to be able to know the column that matches the current date. So for example, Today is 7/07/2014 - that would return Column C. I'd like to be able to have a function/formula that will dynamically change the range based on which column the current date is in for row 1

Let's say I have a cell that I only want to display data from the start of the range and only until the current day.

So the formula in cell B3 would scan line 1 to determine which column contains the current date - in this case on 7/07/2014 the column is C. So it would scan Line 2 from the beginning column A to column C (A2:C2) where "C" is the variable. And it would "count" the number of cells the word "YES" appears in.

The forumula value would change day-by-day as the date would change and the length of the range it scans should be dynamic based on the date.

I hope this makes sense! If there is any clarification needed please let me know!

  • @pnuts - no column A is not sorted. for this spreadsheet no columns will be sorted at any time. – robotsushi Jul 7 '14 at 16:21
  • @pnuts - The row 1 will only contain dates. and they will all be in order. they will not change. What do you mean by "sufficient"? there is no other dates in that column, although I don't know how that's relevant to the question. – robotsushi Jul 7 '14 at 16:31
1

The match formula will tell you which column within 1:1 matches today(); in conjunction with index we can use that column number to get the address of the cell holding the Yes/No data for today. This becomes useful when paired through string concatenation (&) with the address of the first cell of the Yes/No data ($A$2 here) -- the 'dynamic' range you wanted can now be interpreted using the indirect formula!

All together, you're looking at the following formula for B3 in your example:

=COUNTIF(INDIRECT("$A$2:"&CELL("address",INDEX($1:$2,2,MATCH(TODAY(),$1:$1)))),"Yes")

edit

I temporarily forgot about filter, which makes this task much easier: =COUNTIF(FILTER(2:2,1:1<=TODAY()),"No")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.