Making an indirect countifs formula dynamic

  • Hi all


    I'm working with the following formula:


    =IF(AND(SUM(COUNTIFS(INDIRECT({"Q3","R3","S3","T3","U3"}),"Yes",INDIRECT({"Q5","R5","S5","T5","U5"}),P5))>=1,P3="Yes"),"Free","Not Free")


    However if I try to copy this down the cell references do not automatically change for each row (i.e. I'd like "Q3" to become "Q4", "Q5", "Q6" etc. as copied down).


    Does anyone know if it's possible to make this formula dynamic?


    Many thanks

  • Re: Making an indirect countifs formula dynamic


    You could change this:


    "Q3"


    to this:


    "Q"&ROWS(Q$1:Q3)


    You'd need to do this for each cell references in the INDIRECT function.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: Making an indirect countifs formula dynamic


    Thanks Ali, I tried this as follows:


    =IF(AND(SUM(COUNTIFS(INDIRECT({"Q"&ROWS(Q$1:Q3),"R"&ROWS(R$1:R3)","S"&ROWS(S$1:S3),"T"&ROWS(T$1:T3),"U"&ROWS(U$1:U3)}),"Yes",INDIRECT({"Q"&ROWS(Q$1:Q5),"R"&ROWS(R$1:R5),"S"&ROWS(S$1:S5),"T"&ROWS(T$1:T5),"U"&ROWS(U$1:U5)}),P5))>=1,P3="Yes"),"Free","Not Free")


    But got the message 'The formula you typed contained an error'. Any ideas what I'm doing wrong?


    Many thanks


    Helen

  • Re: Making an indirect countifs formula dynamic


    There's a " that shouldn't be there after the R rows declaration.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

Participate now!

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