1

Multiple Countif conditions?

Hi,

Is it possible to do multiple Countif or an alternative formulae that creates the same effect?

Examples (do not work):

COUNTIF(@AB:AB; = "Accounting system" AND @Y:Y;"Company AS") * 2800/1000)

COUNTIF(@AB:AB; = "Accounting system", @Y:Y;"Company AS") * 2800/1000)

COUNTIF(@AB:AB; = "Accounting system") AND COUNTIF(@Y:Y;"Company AS") * 2800/1000)

 

 

Single condition works: COUNTIF(@AB:AB; = "Accounting system")  * 2800/1000)

7 comments

  • 0
    Avatar
    Larona Nyambe

    Hi Rune, 

    Thanks for posting on our community forum. 

    You perform multiple conditions by using the AND(condition 1, condition 2) / OR(condition 1, condition 2) functions. You add the conditions inside these functions. E.g : 

    COUNTIF( AND(@AB:AB; = "Accounting system" , @Y:Y;"Company AS") )

    Cheers,

    L.N

  • 0
    Avatar
    Edujobs Academy

    Hi Rune,

    I have 10 States and for each states have different student status like "Enrolled" and "Idle". How to apply countifs in that area?

  • 0
    Avatar
    Janice Janczyn

    Hello,

    To handle different combinations of criteria, use the AND and OR function as needed. For example, the following counts rows where state = Vermont & status = enrolled or state = Minnesota & status = idle

          COUNTIF( OR( AND( state="Vermont", status="enrolled" ), AND( state="Minnesota", status="idle" ) ) )

    You can add condition combinations as needed to handle other states. For details, refer to Klipfolio's logic functions. The following counts rows where state is found in the list created by ARRAY("Vermont", "Maine") & status = enrolled or state = Minnesota & status  is found in the list created by ARRAY("idle", "active").

          COUNTIF( OR( AND( IN( state, ARRAY("Vermont", "Maine") ), status="enrolled" ), AND( state="Minnesota", IN( status, ARRAY("idle", "active") ) ) )

     

    Thank you,
                 Janice

  • 0
    Avatar
    Edujobs Academy

    Just like you suggested earlier. But see nothing useful results i am getting.

     

  • 0
    Avatar
    Janice Janczyn

    COUNTIF returns a single value: the number of True values returned by the condition inside the COUNTIF. If you would like a value for each row in your table, you'll need a different approach, which (for alignment reasons) depends on how the other columns in your table are built. I suggest you email support@klipfolio.com explaining more about what you are trying to display in this column.

    Thank you,
                 Janice

  • 0
    Avatar
    Rebecca Yarbrough

    What about a COUNTIF for multiple conditions that I want to count when 3 words DO NOT appear in a string? Ex: When there's a column containing cells with strings of data, and I want to count 4 things: number of cells that contain the letter A, number of cells that contain the letter B, number of cells that contain the letter C, and number of cells that do not contain the letters A, B or C. 

  • 0
    Avatar
    Janice Janczyn

    Hi Rebecca,

    COUNTIF can be used with any combination of logic functions. To count the number of cells that contain the letter A: 

         COUNTIF( CONTAINS( cells, "A") )

    If you want this to be case-insensitive, convert your data to uppercase by using the UPPER function: 

         COUNTIF( CONTAINS( UPPER(cells), "A") ). This works similarly for B or C.

    To count the number of cells that do not contain the letters A, B or C requires combining logic functions. OR returns true if 1 of the conditions it contains is true.

         OR( CONTAINS( cells, "A") , CONTAINS( cells, "B") , CONTAINS( cells, "C")  )

    Wrap NOT around this to return the opposite value, since you're looking for cells that do not contain A, B or C.

         NOT( OR( CONTAINS( cells, "A") , CONTAINS( cells, "B") , CONTAINS( cells, "C")  ) )

    Then count the number of true values returned:

         COUNTIF( NOT( OR( CONTAINS( cells, "A") , CONTAINS( cells, "B") , CONTAINS( cells, "C")  ) ) )

     

    Thanks,
             Janice

Please sign in to leave a comment.