PDA

View Full Version : countif and hlookup



wilg19
06-29-2017, 02:03 PM
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
06-29-2017, 03:58 PM
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
06-29-2017, 04:14 PM
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
06-29-2017, 04:23 PM
Can you post a sample workbook?

wilg19
06-29-2017, 04:42 PM
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
06-29-2017, 05:31 PM
=COUNTIFS(J$4:NP$4,"1 Wk Vac FT",$J$1:$NP$1,"<"&C4)

wilg19
06-29-2017, 05:52 PM
That did it! TY