Excel Formula Dynamically

[kashif.special2005]

Hi,

I have Date columns that is dynamically expand or reduce as per date selection by user, my question is how can I dynamically adjust formula, for example my date column starts from Column C, and user select start date is 01-09-2017 (Date Format is DD-MM-YYYY) To 25-09-2017, then the formula would be like below.

=COUNTIFS($C4:$AA4,">75")

and like if user selects Start date 10-02-2017 To 20-02-2017 then the formula would be like below.

=COUNTIFS($C4:$M4,">75")


Note:- There is around 20000 rows data, and the column adding or reduced by VBA, I just want to adjust formula reference dynamically and take the right refference.

=COUNTIFS($C4:$AA4,">75")

I am trying to build a formula dynamically for above formula, however it giving me an error about invalid formula

=COUNTIFS(ADDRESS(ROW(),3,4,1):ADDRESS(ROW(),MATCH("Average of RRC stp FR, DL PW",$2:$2,0)-1,4,1),">75")

I googled it and saw that maybe INDIRECT formula can do the trick, but I also read that INDIRECT formula is a VOLATILE formula and it is heavy formula.

Please guide me how can I achieve this desired result.

Thanks
Kashif

 

[Aladin Akyurek]

Please try to be concise when describing a problem... So, generically:

Either...

=COUNTIFS(C4:INDEX(C4:XFD4,MATCH(…,0)),">75")

Or...

=COUNTIFS(INDEX(C4:XFD4,MATCH(...)):INDEX(C4:XFD4,MATCH(…,0)),">75")<strike></strike>

 

[kashif.special2005]

Hi Aladin Akyurek,

Thank you so much, it working as per requirement.

Thanks
Kashif

 

[Aladin Akyurek]

Hi Aladin Akyurek,

Thank you so much, it working as per requirement.

Thanks
Kashif

You are welcome. Thanks for the update.