Countif formula

[ALT719]

Hi! I have the formula below. I want the formula to count the data from E17 to Q17, even if I add a new column. I am trying to calculate the last 13 weeks of data at all times and do not want the cell references to change when a new column is added. In other words, I add a column and the formula shifts to F17 to R17... but I want it to always be E17 to Q17, even if another column is added. I have tried various versions of adding INDIRECT to the statement and it always returns an error. I am not sure what I'm missing. Any help would be greatly appreciated!

=COUNTIF(E17:Q17,"Yes")/COUNTA(E17:Q17)

 

[arthurbr]

Perhaps

=COUNTIF($E$17:$Q$17,"Yes")/COUNTA($E$17:$Q$17)

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LET(a,TAKE(DROP(D17:R17,,1),,13),COUNTIFS(a,"Yes")/COUNTA(a))

 

[kvsrinivasamurthy]

TRy
=COUNTIF(INDIRECT("E17:Q17"),"Yes")/COUNTA(INDIRECT("E17:Q17"))

 

[ALT719]

=COUNTIF(INDIRECT("E17:Q17"),"Yes")/COUNTA(INDIRECT("E17:Q17"))

This worked perfectly! I'm not sure what I was missing in the Indirect formula I used, but this fixed it! Thank you so much for your help, everyone!

 

[Fluff]

Did you try the formula that I suggested, as it is non-volatile & so more efficient?