Help with COUNTIF syntax pls

Hi pomme'

"do you know whether COUNTIF can be used with AND/OR operators?"

COUNTIFS counts items only if ALL conditions are met.

For OR condition, use a SUMmed series of COUNT statements.

Examples:

The formula shown below the table counts all values in column B that are less than the value in B8 (700) AND greater than or equal to the value in B3 (200):

For OR counts where there is no overlap between the groups, you can use the sum of two or more COUNTIF statements, as shown below this table:

IF counts where some values may meet more than one of the listed conditions can be accommodated using an auxiliary column continig an OR statement that will return TRUE for values meeting any of the required conditions, then using COUNTIF to count the 'true' results in that column:

The first formula is entered in C2, then filled down to the end of column C.

The COUNTIF formula is in D2.

The auxiliary column (C) may be hidden.

Regards,

Barry

I think you'll find any formula whose syntax includes the the word 'expression' and the description 'any expression that returns 'true' or 'false' can use AND or OR as part of that expression.

The 'operationIF' functions, which apply a condition to to a series of values to determine which to include in a total or and average, etc. do not accept AND or OR functions.

As with all blanket statements, it may be possible to fnd exceptions. :)

Hi pomme4moi,

COUNTIF is a weird one in that it wants its input as a text string. So,

C2=COUNTIF(B,">"&C2)

quinn

Hi pomme4moi,

COUNTIFS should satisfy your countif with multiple conditions. Don't know how your would get AND/OR to work.

quinn