Forum Discussion
Is it possible to Rank repeating strings without repeating same ranks in an Excel Table?
I see. Excel Table "remembers" that, for example, C5 is in the last row of the table. If you expand the table it adjust formula in row 5 such way it reference on the last row of expanded table. Thus you have two formulas with C6 an no one with C5.
Workaround could be
=[@Date]&[@Fruit]& COUNTIFS( $C$2:INDEX([Fruit],ROW()-ROW(Table1[[#Headers],[Fruit]])), [@Fruit], $B$2:INDEX([Date],ROW()-ROW(Table1[[#Headers],[Date]])), [@Date])
Thank you so much sir SergeiBaklan
I have that same follow up question
I've always wondered that hardcoding values would save up on the calculation speed.
Writing 1 instead of referencing header row, would prevent one wheel of calculation, in case the
sheet is very heavy.
Is there any evidence of this?
That's always a compromise - save few milliseconds on recalculation of few hours of your own time on maintenance. Depends on concrete case, if recalculation is really slow when it's worth to re-build formulae.