Dynamic Range in Formula from text

[subrato312]

Dear All,

I have a list of criteria with different "IF" conditions for all criteria. i.e for criteria1 "GGG" it should use If(xxxx) formula and for criteria "GGR" it should use If(yyy) formula. As the no. of criteria is large and all have unique If formula (around 64) , I need to apply each unique "IF" formula on my data.

My method:I wrote all "IF" formula for 64 criteria (text format) and used vlookup to call the formula and using "Evaluate" in Name Manager to apply the formula. But the problem is in all "IF" formula the Range is fix as written in formula. How to make this dynamic in datasheet. (Z7:AE7 as shown below)

criteria1 = IF(COUNTIF(z7:ae7,"G")>3,"R"...........) -
criteria1 = IF(COUNTIF(z7:ae7,"G")>2,"G"...............)

attachment: Pls check attachment, there will be easier way to do I guess. Based on criteria for first three time periods, (there are 64 criteria )each criteria requires counting G,B+,B- and R and accordingly call it as A/B/C/D
Thanks for your time