Using OR in a SUMIFS or COUNTIFS Function

Atilla

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

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

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

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