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

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

## Participate now!

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