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

 

 

 

 

 

 

 

 

excel
Formulas and Functions

2 Replies

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

     

     

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 27, 2022

    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.

Resources