Scan row for value, get column of that match, apply column in another formula in a different cell

Question

+------+--------------+--------------+--------------+--------------+--------------+
|      |      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!

Answer 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")