Countif and Variable Date Range

**neilfleming** · 09-26-2012, 11:10 AM

I have a time off tracker that has individual days as headers in the columns, e.g., | 1/1/2 | 1/2/12 | 1/3/12 |, etc.

In the rows, I have employee names, e.g.:
Jack
Nancy
Tom

Within the cells of the grid, I track different types of days off, like "V" for vacation, "S" for sick, "H" for holidays, etc.

In a cell at the top of the spreadsheet, I have today's date "=Today()"
What I would like to do is, for a given employee, count the number of V's between 1/1/12 and today's date in his particular line.

The MATCH function will certainly tell me how many columns over I have to include in the COUNTIF range, but I can't seem to combine MATCH into the COUNTIF function. Can I do this, or have you other suggestions?

Thanks...Neil

**Fotis1991** · 09-26-2012, 11:20 AM

Give a try to COUNTIFS function.

**neilfleming** · 09-26-2012, 11:33 AM

Sorry, off the mark. It's easy to countif the number of occurrances of "V" within a range of cells in the row starting from B2 through MN2. It is the act of making the MN2 variable that I'm having trouble with. Thanks for the try, though.

...Neil

**neilfleming** · 09-26-2012, 11:48 AM

I think I found it:

=COUNTIF($B2:*(OFFSET($B2,0,MATCH($A$1,$B$1:$MN$1,0)-1)),"V")

(Don't worry about the *, it's to stop the software from turning part of the formula into a smiley face.)

...Neil