Using OR in a SUMIFS or COUNTIFS Function

Atilla · 02/07/18

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 · 02/07/18

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 · 02/08/18

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 · 02/09/18

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 · 03/07/18

Try this:

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

LoriP · 06/22/18

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 · 01/16/19

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 · 02/13/19

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

Comments