# Problem with combining 2 functions: Choose and CountIF

Occasional Visitor

# 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 & "|" & 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

# Re: Problem with combining 2 functions: Choose and CountIF

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.

# Re: Problem with combining 2 functions: Choose and CountIF

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