I have this google sheets input.
| Players | Loot |
|---|---|
| Player1 | 4 |
| Player2 | 1 |
| Player3 | 4 |
| Player4 | 2 |
What I'm seeking to do is to simplify this formula.
=AND(B2>=3,B3>=3,B4>=3,B5>=3)
This is what i did so far. as suggested by @Harun24hr in this comment.
=ArrayFormula(COUNTIF(B2:B5>=3,"TRUE"))=COUNTA(B2:B5)
Can this formula be simplified further and still get TRUE in a single cell when all of Loot values is >= 3?
CodePudding user response：
It's hard to understand exactly what you mean by simpler, but this formula is dynamic which I would consider simpler....?
=Count(filter(B2:B,B2:B>=3))=Count(B2:B)
Also, I try to avoid using CountTf after @ScottCraner mercilessly taught me the in the comments sections of one of my less impressive answers from a few years ago by saying:
COuNTIF is a calc hog. Fill column A with the row number out to 100,000 rows and then add the formula: =OR(COUNTIF(A:A,A1)) and fill down the same number of rows. Depending on your machine this may take a while. =ISNUMBER(MATCH(A1,A:A,0)) is much quicker.
My computer pretty much melted (though that was an Excel Discussion while this is Google sheets?).
CodePudding user response：
Select the entire range B2:B5 and check if it's greater than or equal to 3 using >=. >= operator supports arrays. AND merges the values.
=ARRAYFORMULA(AND(B2:B5>=3))
CodePudding user response：
try:
=SUMPRODUCT(B2:B>=3)=COUNTA(B2:B)