# Using OR in a SUMIFS or COUNTIFS Function

edited 12/09/19

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.

Tags:

• ✭✭✭✭✭✭

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?

• 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??

• ✭✭✭✭✭✭

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.

• Try this:

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

• 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

• 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!

• ✭✭

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!