countif and hlookup

[wilg19]

Hi. Need to countif how many instances of "1 Wk Vac FT" from column J34 to column NP34 where if date in row 27 of same columns is less than C34(anniversary date). I am unable to find solution on a index match or hlookup in this case. All help appreciated.

[mdmackillop]

Not sure if this is what you're after; (adjust date as required)
=IF(C34<TODAY(),COUNTIF(J34:NP34,"1 Wk Vac FT"),0)

[wilg19]

Hi, to explain further. I need to count instances of "1 Wk Vac FT" from J34 to NP34 where in row J27 which holds dates June 26th in J27, June 28th in K27 etc.. where it counts instances up to the date in C34 (eg Nov 22 2017). So it references the dates in row 27 until it finds the date in C34 then stops counting.

Hope this explains further.

[mdmackillop]

Can you post a sample workbook?

[wilg19]

I uploaded an eg. In cell D4 is my countif formula that I need to adjust counting only instances of "1 Wk Vac FT" etc.. as long as the dates in J1 to NP1 are less than the anniversary date in C4.

Hope this helps.

[mdmackillop]

=COUNTIFS(J$4:NP$4,"1 Wk Vac FT",$J$1:$NP$1,"<"&C4)

[wilg19]

That did it! TY