Using COUNTIFS with OR function spread across multiple ranges

Cara Tupps
edited 03/15/22 in Formulas and Functions

Hello,

I am trying to build a COUNTIF function that will count all cells which meet criteria 1 and either criteria 2 or 3, which are found in different ranges.

Example: In Sheet1, I have the following columns: shape, color1, and color2. In Sheet 2, I would like to create a function which counts all observations in Sheet1 where shape=square, and either color1=blue OR color2=blue.

If applied to the data set below, the function in Sheet2 should correctly return '2' :

Sheet1

shape color1 color2

square blue green

square red red

circle blue red

square red blue


Can someone please help me with the correct syntax for this function? Thanks in advance!

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You are going to need two separate COUNTIFS and add them together.

    =COUNTIFS({Sheet 1 Shape Column}, @cell = "Square", {Sheet 1 Color 1 Column}, @cell = "Blue") + COUNTIFS({Sheet 1 Shape Column}, @cell = "Square", {Sheet 1 Color 2 Column}, @cell = "Blue")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Intern98
    Intern98 ✭✭✭

    you can use this

    =COUNTIF(([Shape]@row), AND(([Shape]@row = "square"), (OR(([color1]@row = "blue"), ([color2]@row = "blue")))))

    as a column function, on a new column for instance, [new colum].

    use : =countif([new colum]:[new colum],1) to get total count

  • Hi,

    Thanks for your response, but this returned an UNPARSEABLE error. I forgot to mention that the color1 and color2 columns are actually multi-select and thus contain multiple responses, so I think the CONTAINS function should also be nested inside the formula. The adjusted example would be:

    Sheet1

    shape...................color1.....................color2

    square..................blue, green.............green, red

    square..................red, green...............red, red

    circle....................blue, red, black.......green

    square..................red, blue..................blue, green


    Here's what I have so far, per the example:

    =COUNTIF(([shape]@row), AND(([shape]@row = "square"), (OR(CONTAINS("blue", [color1])), (CONTAINS("blue", [color2])))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You are going to need two separate COUNTIFS and add them together.

    =COUNTIFS({Sheet 1 Shape Column}, @cell = "Square", {Sheet 1 Color 1 Column}, @cell = "Blue") + COUNTIFS({Sheet 1 Shape Column}, @cell = "Square", {Sheet 1 Color 2 Column}, @cell = "Blue")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • That worked! Thanks, Paul.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Aric Dodd
    Aric Dodd ✭✭
    edited 12/26/23

    I have something similar I need help with.. I need to create a sheet summary formula that will count IFs the first qualifier is the [Project started]:[Project started] column contains a check mark. The second qualifier is if the [Status]:[Status] column contains either "06 - Project Engaged" or "07 - On Hold (See Notes)".

    What I have now is not working.

    =COUNTIFS([Project started]:[Project started], 1, OR([Status]:[Status], "06 - Project Engaged", [Status]:[Status], "07 - On Hold (See Notes)"))



    Disregard, I figured it out.. =COUNTIFS([Project started]:[Project started], 1, Status:Status, "06 - Project Engaged") + COUNTIFS([Project started]:[Project started], 1, Status:Status, "07 - On Hold (See Notes)")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!