 # 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.

• Re: Using a value from a cell inside a formula

Try:

[COLOR="#0000FF"]=COUNTIF(INDIRECT("\$C\$16:\$C\$" & F1),\$Z3)[/COLOR]

or better
[COLOR="#0000FF"]
=COUNTIF(INDEX(C:C,16):INDEX(C:C,F1),\$Z3)[/COLOR]

Where there is a will there are many ways. Finding one that works for you is the challenge!

• 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

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

Try:

[COLOR="#0000FF"]=COUNTIFS(INDIRECT("\$C\$16:\$C\$"&\$F\$1),\$Z3,INDIRECT("\$T\$16:\$T\$"&\$F\$1), "<" & D1)[/COLOR]

Where there is a will there are many ways. Finding one that works for you is the challenge!

• 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

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

Where there is a will there are many ways. Finding one that works for you is the challenge!

• 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?

• Re: Using a value from a cell inside a formula

There are quite a few exce3l functions that either work a little differently - or not at all - in google sheets

Regards

Ford

• Re: Using a value from a cell inside a formula

Funny but I just copied and pasted my formula into Google Sheets, I entered a date in D1, I entered a value in F1 for last row, and I entered a random value in Z3, then I entered values in column C starting at C16 and entered dates starting in T16 making sure that at least one combination of entries in C and T matched the criteria in the formula set by the values in D1 and Z3. I did get expected results.

So, in conclusion, check your date in D1 and in column T to make sure they are true date entries. Make sure you have a value in Z3 that matches some values in column C (and that they are the same format). Make sure that the matching combination (if any) are within the range set by the value in F1.

Where there is a will there are many ways. Finding one that works for you is the challenge!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!