Using a value from a cell inside a formula

I have a formula in cell F1 which is =COUNTA(A:A)+1 which returns values depending on the number of rows having values in have in column A, every time I change the data from a CSV file.

I want to use the value returned by this formula in another formula elsewhere.

For example: If the value returned by the formula is 425, I want to use this value in another formula referring to the cell F1.

The formula that I want this to be added to is =COUNTIF($C$16:$C$2000,$Z3 ). I want the value from F1(425) to be included in the place of 2000 hence the formula should effectively return the value of =COUNTIF($C$16:$C$425,$Z3 )

I tried using & to append using the & symbol annd also tried using concatenate function to append. In both the cases, I did not arrive at the same result if I use the formula: =COUNTIF($C$16:$C$425,$Z3 ) , perhaps I am not using them correctly.

Can someone help please!

Re: Using a value from a cell inside a formula

Quote from NBVC;773024

Try:

=COUNTIF(INDIRECT("$C$16:$C$" & F1),$Z3)

or better

=COUNTIF(INDEX(C:C,16):INDEX(C:C,F1),$Z3)

Thank you so much NBVC. That worked perfectly.

Now, I am trying to do something similar where I am using another CountIFS formula which is =COUNTIFS(INDIRECT("$C$16:$C$" & $F$1), $Z3, INDIRECT("$T$16:$T$" & $F$1), "<06/24/2016").

Here, instead of mentioning the date directly, I would like to refer it to the cell D1 where I will change the date based on the date range of the report. I tried the below two options which are not giving me the desired results. Please help.

=COUNTIFs(INDIRECT("$C$16:$C$" & $F$1), $Z3, INDIRECT("$T$16:$T$" & $F$1), indirect("<" & D1)

=COUNTIFs(INDIRECT("$C$16:$C$" & $F$1), $Z3, INDIRECT("$T$16:$T$" & $F$1), "<" & indirect(D1))

Re: Using a value from a cell inside a formula

Quote from NBVC;773029

You don't need the INDIRECT to reference the date...

Try:

=COUNTIFS(INDIRECT("$C$16:$C$"&$F$1),$Z3,INDIRECT("$T$16:$T$"&$F$1), "<" & D1)

I tried this formula but it is not working. Can you please suggest any alternate formula?

Re: Using a value from a cell inside a formula

Quote from NBVC;773034

Do you have an actual date in D1? I tested it and it works fine for me.

The formula you suggested works perfectly in excel but I am afraid the same doesn't work in google sheets which is quite surprising. I made sure that the format of both the ranges used in the formula is the same. Any solutions?