Count if with multiple conditons

Hi there,

How do to count if column 1 is equal to "A" or "B" and Column 2 is equal to "C "

Answers

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni

    Use COUNTIFS and OR and @cell

    @cell is a funny Smartsheet thing that basically means "as you're going down the column, check each cell to see if it meets these criteria"

    the basic COUNTIFS format is essentially an AND statement.

    =COUNTIFS(range 1, criteria 1, range 2, criteria 2) means "count 1 if, on a row, the value in column 1 meets criteria 1 AND the value in column 2 meets criteria 2"

    When you want to add an OR to your AND, then you insert that using the OR(criteria, criteria) function.

    So your COUNTIFS then becomes

    =COUNTIFS (range 1, OR(@cell=criteria 1a, @cell=criteria 1b), range 2, criteria 2), which means "count 1 if, on a row, the value in column 1 meets either criteria 1a OR criteria 1b AND the value in column 2 meets criteria 2.

    So then finally this becomes for your specific example…

    =COUNTIFS( [Column 1]:[Column 1], OR( @cell="A", @cell="B"), [Column 2]:[Column 2], "C")

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN