Thank you, CARLOS for your comment. You have to use the correct array (Sales Rep
or B5:B14) in INDEX function and Rank Column (D5:D14) in MATCH function. Also, while using the Fill Handle icon, you have to freeze both arrays. The most important part, you must write Rank 1,2,3.. manually in General format in F column.
There may have extra space or Apostrophe (‘) in the F column where you insert Rank numbers manually. You should remove all extra spaces.
You can see our article related MATCH function error. The link is: https://www.exceldemy.com/excel-match-function-not-working/#Case_1_NA_Error
For getting basic idea of INDEX-MATCH function you can see the examples from this article https://www.exceldemy.com/excel-index-match-example/
Still, you are facing the problem then please comment with your used formula and sample dataset.
Regards
Musiha Mahfuza Mukta| Team Exceldemy.

Hi ATIF, Thanks for your comment. Here, I made a dataset keeping the Text value in A column, Date value in B column and Counter will be in C column. If you provide your Excel file, then it will be more useful. As per my understanding, I am providing the following VBA code.
From Developer tab >> go to Visual Basic >> Insert a Module >> copy the code in that >> from Macros >> Run the code.

Sub Days_Counter()
Count = 0
For i = 1 To 11
If Range("B1").Cells(i) <> Range("B1").Cells(i + 1) _
And Range("A1").Cells(i) = "Normal" Then
    Count = Count + 1
    Range("C1").Cells(i) = Count
Else: Range("C1").Cells(i) = 0
End If
Next i
End Sub

You can see the outcome below.

Where the counter counts single value for same date. I have highlighted the same date. Also, for “Abnormal” text the counter didn’t count.
In the code, you must mention total Row number of your dataset in For Next loop.
If you want only the text part, you can remove this portion

Range("B1").Cells(i) <> Range("B1").Cells(i + 1)

from code.
Hopefully this will work. If you are still facing problem, then please comment with more details or the sample dataset.
Regards
Musiha Mahfuza Mukta| Team Exceldemy

Thank you ZOYSA for your comment. Below, I have attached two formulas for your problem.
I have written the data from the A2 cell and C2 cell. According to your question, I have considered the dataset till A10 and C10.
Firstly, write the below formula in the E2 cell or the cell from where the NAME will be started.
=IF(A2=”TOM”,C2,””)

Then copy this formula up to E10 or your dataset’s end cell.
Then use another formula in the F4 cell.
=SUM(E2:E10)

Thank you ROB for your comment. Here, ROW function is working as the row index number of the INDEX function. Actually, it should be ROW(B2:D9)=> {2;3;4;5;6;7;8;9} which denotes respectively the 2nd, 3rd, 4th…. up to 9th row number of this ($B$4:$D$12) array of INDEX function. Here, we ignore the 1st row of ($B$4:$D$12) this array as 1st row contains the Club Name’s not any player Name’s.
On the other hand, you have to use B4:D4 row in the MATCH function which will search for the Club Name’s and act as the column index number in the INDEX function.
Say, when you choose Borussia Dortmund or C4 cell from the list of C14 cell then the MATCH function will return 2 and thus the column index number of INDEX function will be 2 so INDEX function will give all the rows (except 1st one) of 2nd column of the given array which means all the player name’s of the Borussia Dortmund club.
So, you can use the formula like the below one. =IFERROR(INDEX($B$4:$D$12,ROW(B2:D9),MATCH($C$14,$B$4:$D$4,0)),””)

Hello, J KUMAR.
Thank you for your comment. I have tried the code too and the code is absolutely right and perfectly working. But you are facing problem because most probably your device is running out of virtual memory. So, when you are trying this code in Excel, at that time you should close all other applications. Also, you should use an individual module for this code.