Problem with combining 2 functions: Choose and CountIF

Occasional Visitor


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 Replies


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



=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.


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

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

To generate the whole column requires


and returning the count of a specific string

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