Using OR in a SUMIFS or COUNTIFS Function

edited 12/09/19 in Formulas and Functions

Hi Guys,

Smartsheet noobie here and I was wondering if it is possible to use a OR in SUMIFS or COUNTIFS function.


Basically I want to sum a count values in a particular columns if either one of 2 criterias a met in another column.

My basic syntax is along the lines of:  Sum all the values in column A, if column B has value "Quoted" OR " Won".

Im hoping that makes sense. if so, what would be the basic syntax of my function?

Thanks for any help.






  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Hi Atilla, you cannot use an or statement currently in a countif or sumif, but you can work a simple math equation and add to countifs together. 

    i.e. =Countif([Column Name]:[Column Name], "blue")+Countif([Column Name]:[Column Name], "green")

    Would give you the count of all occurrences of the word Blue, plus all occurrences of the word "Green" 

    Does that make sense? 

  • Atilla

    Thanks Mike much appreaciated...

    How bout using AND....what Im trying to do for example is:


    Column A: Numbers

    Coumn B: Colours (ie "Blue")

    Column C: Days of the Week (ie "Friday")


    Hence the syntax is something like...

    Sum up all the values in column A, if column B is "Blue" AND column C is "Friday"


    Is this doable??



  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Absolutley, Try this: 

    =sumifs([Column A Name]:[Column A Name], [Column B Name]:[Column BName], "Blue", [Column C Name]:[Column C Name], "Friday")

    That is considering that the day of the week is a text field, not being pulled from from a date. 

    You can also add those together if you wanted by putting a + sign between them and adding the formula again with different criteria.

  • Wade Harshman

    Try this:

    =SUMIFS(ColumnA1:ColumnA20, ColumnB1:ColumnB20, OR(@cell = "Yes", @cell = "Quoted", @cell = "Won"))

  • LoriP

    this is really close to something I'm trying (and failing) to do.  With the same scenario how would you code in to include Friday and Saturday


  • Dave Benton

    After 2 hours of hunting and just about to give up I find the solution to getting an OR in a SUMIFS using a picklist. Thanks you Wade!

  • Manuelf
    Manuelf ✭✭

    Can you explain how, I think this might function to solve a problem I have.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!