Counting number of responses and showing top 5

I am making a doc that tracks the spelling words for my son, and how often he gets them right and wrong. Just think digital flash cards. I want to be able to show what his top 5 challenge words are. I was able to figure out the top 5 formula thanks to @Dan_Rose and his tip using Slice(). Anyway, can someone help me figure out how to do the counting formula?

The table has three columns: Word, Date, Correct/Incorrect. So each time I press either the correct or incorrect button for the word, it creates a new record and records those three fields in the table. Any help would be greatly appreciated!

If you just need to know how many times he was incorrect with the Top 5, I think, this should do it (in a new column in the table Top 5) :

Lookup([Progress Data],Word.Word, thisRow.Word.Word).Filter([Correct/Miss]="incorrect").Count()

If it’s for the Progress Data table, I’ve got this (still for a new column in the table):
I’ve more or less checked the results and it seemed correct :wink:

If(thisRow.[Correct/Miss]="incorrect",[Progress Data].Filter(Word .Contains(thisRow.Word) AND [Correct/Miss]="incorrect").Count(),[Progress Data].Filter(Word.Contains(thisRow.Word) AND [Correct/Miss]="correct").Count())

You can divide this formula in 2 columns if you want/ need to :wink:

Thank you for your reply! Now I might not have followed it correctly, but I don’t think this is quite it. It gives me the correct count of times it was incorrect, but it does not show the UNIQUE words in the top 5 list, as this image shows:

image

Is there a way to show just the unique count of the top five?

Since you’re ranking words, I’d recommend you creating a view of the All Words table.

So you add a new column to that table using a slight variation of @Pch’s formula:
[Progress Data].Filter(Word=thisRow and [Correct/Miss]="incorrect").Count()

Then you’d also alter the filter accordingly:
thisRow.in(thisTable.Sort(false,[New Row Title]).Slice(0,5))

2 Likes

@Chad_Oglesbay : My bad ! I didn’t think about that. But I’m glad you’ve found your the solution :blush: !

@Dalmo_Mendonca : Thank you for pointing that out and correct my mistake :blush: !