Tech2 Touch

Tag: Conditional formula

  • Using Excel Multiple condition COUNTIF function

    In my previous post, we have seen how to use single criteria Excel COUNTIF function. That works great  when you need to count a specific range of cells based on one condition.  Some times, there are situation we are required to count range of cells based on 2 or more conditions.  So let us have a quick look on the same.

    Excel Multi condition count function  with AND:

    The And criteria count function counts the cells if all the conditions are met.  The simple example for this is counting a range of cells which falls in between a numerical value. For example, counting cells that contains a value greater 0 and less than or equal to 20. Any cell that has positive value less than or equal to 20 will calculated in the Excel multi count with AND.For using this we need to use COUNTIF excel function or SUMPRODUCT function.

    Syntax of COUNTIFS function:

    =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

    Where
    criteria_range1:  Required. The first range in which to evaluate the associated criteria.
    criteria1 :  Required. The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. For example, criteria can be expressed as 32, “>32”, B4, “apples”, or “32”.
    criteria_range2, criteria2, …    Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

    Note: COUNTIFS works only with Excel 2007 and higher versions. For using multiple condition count in earlier version of Excel we need to sumproduct.

    Syntax for using SUMPRODUCT function as multicondition count:

    As we have seen earlier, SUMPRODUCT function can be used to count range of cells based on multiple criteria.
    Example for using Multicondition count in Excel: Below the sample data.

    Counting only the sales of Apple for the month of Jan with value less than 250:
    1. Using countifs function: =COUNTIFS(A2:A13,”Jan”,C2:C13,”Apple”, D2:D13,”2. Using the SUMPRODUCT function: =SUMPRODUCT((A2:A13=”Jan”)*(C2:C13=”Apple”)*(D2:D13
    250>250>

    Using Multiple condition count with OR:

    To count cells by using an OR criteria, you should use multiple COUNTIFS functions.  For example, counting the number of instances of  8,6 and 5 in the range B2:B13.
    =COUNTIF(B2:B13,8)+COUNTIF(B2:B13,6)+COUNTIF(B2:B13,5)

    This can be achieved through COUNTIF function array formula of Excel.  The below Array formula returns the same result as that of above. As this is a array formula you need to enter the formula using Ctrl+Shift+Enter.
    =SUM(COUNTIF(B2:B13,{8,6,5})

    Note:  You need use flower bracket for 8,6,5 in the above example. 

  • Excel COUNTIF function Syntax with Examples

    The Excel COUNT and COUNTA formula is useful for the basic counting requirements. However, sometime you need to count a number of cells in a range which meets given criteria. To do this, COUNTIF formula is useful. So COUNTIF formula counts number of cells in a range based on the given criteria.
    Syntax of  Excel COUNTIF formula:
    COUNTIF(range, criteria)
    where
    range is range of cells which you need to count
    criteria is nothing but the condition on which the range should be calculated.

    Example for Excel COUNTIF Formula:
    Let us take below sample data and use countif function with different criteria.

    Excel Examples

    =COUNTIF(C2:C7,50) :- Counts the range with Value 50
    =COUNTIF(C2:C7,E1) : Counts the cells value equal to Range E1
    =COUNTIF(C2:C7,”>”&E1) : Counts the cells greater than value specified in the range E1
    =COUNTIF(D2:D7,”=COUNTIF(C2:C7,”*”): Counts the cells which contains text
    =COUNTIF(A2:A7,”Apple”): Counts only the cells with exact word Apple; not case sensitive
    =COUNTIF(A2:A7,”*Apple*”): Counts cells containing text Apple anywhere within the text
    =COUNTIF(A2:A7,”*E”) : Counts cells which cell content has the alphabet E in the last
    =COUNTIF(B2:B6,”>”&AVERAGE(B2:B6)) : counts the cells which are greater than average of the range.

    Using COUNTIF function with named ranges:  You can use Named Ranges in Excel countif function.  For doing this first we need to define the range.  Let us look into the above example and define Range A1:A7 as ” “Data”.

    Excel Tips

    Now we can use the names instead of writing the Ranges. The example for Countif function with named ranges.
    =COUNTIF(Data,”Mango”)
    Similarly we can use defined names to any range by naming that range. It is useful when we use complicated countif functions.0>