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)
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
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?
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
Just like you suggested earlier. But see nothing useful results i am getting.
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
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.
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
