# Counting instance of a string in a Google Sheets range

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?

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(
• Thank you, this was perfect for my needs. I was unaware of `BYROW`'s existence. I did need to change `COUNTA(row) - 1` to `COUNTIF(row, "A")` however. This may be a stupid question but my brain is mush at this point; if I were to wrap that in a `FILTER`, how do I reference the second returned column for the condition? I hadn't considered the condition would need to be a virtual range. Dec 8, 2022 at 11:20
• Never mind. I figured it out with another `BYROW`. Not sure if it's the best way but it works :) Dec 8, 2022 at 11:29