Problem with combining 2 functions: Choose and CountIF

[myroosta]

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 & "|" & Sheet1!$C$31:$C$39 )

2) then CountIf Function and It worked well:

=COUNTIF( H4#; "1|5|1" )

3) But when I combined the two functions, I got an error:

=COUNTIF( CHOOSE({1};Sheet1!$A$31:$A$39 & "|" & Sheet1!$B$31:$B$39 & "|" & Sheet1!$C$31:$C$39 ); "1|5|1" )

[#2]

@myroosta 

COUNTIF requires a range as first argument. It does not accept an array such as the result of CHOOSE.

Try

=SUMPRODUCT(--(CHOOSE({1};Sheet1!$A$31:$A$39 & "|" & Sheet1!$B$31:$B$39 & "|" & Sheet1!$C$31:$C$39 )="1|5|1"))

If you don't have Microsoft 365 or Office 2021, you may have to confirm the formula with Ctrl+Shift+Enter.

[#3]

@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})))
  ))