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


    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!