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
Sheet1
Name | Rarity |
---|---|
Item #1 | Rare |
Item #2 | Epic |
Item #3 | Epic |
Item #4 | Common |
Item #5 | Legendary |
Item #6 | Rare |
Sheet2
Item | Some check | Another check | So on | and so on... |
---|---|---|---|---|
Item #1 | A | A | B | |
Item #2 | A | B | A | |
Item #3 | B | A | A | |
Item #4 | B | A | A | |
Item #5 | A | |||
Item #6 | A | A | A |
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 MAX()
.
Item | Count |
---|---|
Item #1 | 2 |
Item #6 | 3 |
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?