Indirect Formula Bogs Down Spreadsheet - Alternatives?

I Have a spreadsheet that tracks sessions per month. Each month occupies 9 columns then the next month starts. I want to count all the F2F (named cell) in one month. I set up two cells that determines the start and end column when a month is selected from a dropdown.

I was able to use the indirect formula to count the number of times F2F was entered for each row however once I get to hundreds of rows, it gets bogged down in calculating. Through research I understand that it might be the INDIRECT function that is resource heavy and is likely causing the long calculating.

Is there an alternative formula that would achieve the same value?

i.e. If I choose September then the cells have the value of:

Cell C1 =SUBSTITUTE(ADDRESS(1,VLOOKUP(G2,Constants!B:C,2,FALSE),4),"1","") = CO
Cell D1 =SUBSTITUTE(ADDRESS(1,VLOOKUP(G2,Constants!B:C,2,FALSE)+8,4),"1","") = CW

Cell B6 =COUNTIF(INDIRECT(($C$1&ROW()&":"&$D$1&ROW())),F2F) ' thus COUNTIF(CO6:CW6,F2F)

Is there a less intensive formula? I tried to look at index and match but I am not sure I understand it well enough. It is the yellow cells in the attachment.

Thank you for any help!

Is there a special reason that you need to divide a month in three parts?

Change B5:D5 as shown and use, in B6, copied across and dnown:

=SUMPRODUCT(--(INDEX($U6:$DX6,,MATCH($G$2,$U$4:$DX$4,0)+SEQUENCE(,9,0))=B$5))

Any comments on Post 4???

in B6

Formula:

Please Login or Register  to view this content.