Skip to content

Excel’s SUMPRODUCT with Multiple Criteria Including AND and OR Criteria

    In a previous tutorial we explained how to use SUMPRODUCT to count or sum values based on a single criteria.  In this tutorial we want to explore how to include multiple criteria in your SUMPRODUCT formula – multiple criteria using AND or OR logic.

    Here is our scenario featuring a database of accounts…

    Database

    Counting with Multiple Criteria

    AND Criteria

    Our first task is to count the number of accounts that were over budget in 2016. If you are not familiar with entering criteria in a SUMPRODUCT formula, please see our tutorial here.

    This formula, requires AND logic: both criteria need to be met. To enter AND criteria you have to multiply.  There are two ways to multiply within a SUMPRODUCT function.

    The first option is to use multiple array arguments, as shown below.

    =SUMPRODUCT(--(E2:E11>D2:D11),--(YEAR(C2:C11)=2016))

    The second option is to use a single array argument and multiply the two criteria.

    =SUMPRODUCT(--(E2:E11>D2:D11)*--(YEAR(C2:C11)=2016))

    If we evaluate the two criteria, we can see how the calculation is achieved…

    =SUMPRODUCT({0;1;0;1;1;1;0;1;1;1},{0;0;0;0;0;0;1;1;1;0})

    The position of the 1s in both array arguments only coincide twice, therefore the result is 2.

    OR Criteria

    Our next task is to count the number of accounts that are over budget in 2014 and 2015. There is a mixture of AND and OR logic here.  The logic goes like this…

    Over Budget AND (2014 OR 2015)

    or

    Budget * (2014 + 2015)

    To perform OR criteria you need to add as shown below

    =SUMPRODUCT(--(E2:E11>D2:D11),--(YEAR(C2:C11)=2014)+--(YEAR(C2:C11)=2015))

    If I evaluate the three criteria, you can see how SUMPRODUCT will add the 1s and 0s in the array2 argument and then multiply the result by the 1s in the array1 argument.

    =SUMPRODUCT({0;1;0;1;1;1;0;1;1;1},{1;0;1;0;1;0;0;0;0;0}+{0;0;0;1;0;0;0;0;0;0})

    The result is 2.

    Summing with Multiple Criteria

    If you are unfamiliar with how to use SUMPRODUCT to sum based on criteria, please see our tutorial.

    To sum values rather than count them, you need to include an array argument that references an array of values or performs a calculation on array of values.  For example, say we want to total the overspend in 2016, we would use this formula.

    =SUMPRODUCT(--(E2:E11>D2:D11),--(YEAR(C2:C11)=2016),E2:E11-D2:D11)
    Tags: