Counting instance of a string in a Google Sheets range

Question

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?

Answer 1

Since you already have a formula that filters the data the way you need, you can just apply a byrow() lambda to get the first value in each row, together with the count of the rest of the items in the row, like this:

=byrow( 
  filter( Sheet2!A2:E, countif( filter(Sheet1!A2:A, Sheet1!B2:B = "Rare"), Sheet2!A2:A ) ), 
  lambda( 
    row, 
    { index(row, 1, 1), counta(row) - 1 } 
  )
)