0

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?

1 Answer 1

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 } 
  )
)
2
  • 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.
    – Spedwards
    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 :)
    – Spedwards
    Dec 8, 2022 at 11:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.