I have a few sheets available to me and I'm currently making a dashboard-esque sheet that just displays quick facts about the data as a whole.
Before I explain what I'm trying to do, I will share examples of my two main sheets
|Item||Some check||Another check||So on||and so on...|
This sheet currently has 12 columns after the
Item but this will increase in future.
So what I'm trying to do is to get the item(s) of a specific rarity that has the most of a specific string in sheet 2. I'm able to filter the items by rarity using
FILTER(Sheet1!A2:A, Sheet1!B2:B = "Rare"). I'm then able to use that filtered list to get the corresponding rows of sheet 2 with:
FILTER(Sheet2!A2:E, COUNTIF(FILTER(Sheet1!A2:A, Sheet1!B2:B = "Rare"), Sheet2!A2:A))
What I can't figure out now is how to get the count of each instance of the text per row. Wrapping that last formula in a
COUNTIF() just counts all of them together.
What I'd like to get to is a table where the first column is a list of rare items, with the second column being a list of the count (as below). From there I can simply run a filter on it checking if the count is equal to the
Given what I have thus far, how do I get to where I want to be? How do I count each instance of the text per row?