Forum Discussion

myroosta's avatar
myroosta
Copper Contributor
Apr 27, 2022

Problem with combining 2 functions: Choose and CountIF

Hi, I had a challenge in Excel, Please see the image below ( Data as an example):   1) I use Choose Function and It worked well: =CHOOSE({1};Sheet1!$A$31:$A$39 & "|" & Sheet1!$B$31:$B$39 & "|" &...
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Apr 27, 2022

myroosta 

As a 365 user I have further tools in the bag.  The first step is to define a Lambda function to form a concatenation.

CONCATλ 
= LAMBDA(record, TEXTJOIN("|", ,record))

To generate the whole column requires

= BYROW(Table1,CONCATλ)

and returning the count of a specific string

= COUNT(
    IF(BYROW(Table1, CONCATλ)="1|4|3", 1)
  )

Mind you, with Lambda functions the concatenation step can be bypassed and the rows compared directly

= SUM(N(
     BYROW(Table1, LAMBDA(record, AND(record={1,4,3})))
  ))

 

 

Resources