Using OR in a SUMIFS or COUNTIFS Function

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.

Find more posts tagged with

Sales

Formulas

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.

Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Countifs result not matching filter
In my sheet, I have a filter for 2 values (see below images). The result is 294 In my report this formula yields 304. =COUNTIFS({helper-child}, "1", {gapStatus}, <>"Rejected (not a GAP)", {gapStatus}, <>"removed - duplicate", {gapStatus}, <>"removed - not valid") Why are they not matching? What am I missing?
Using CONTAINS in a MAX(COLLECT) function
I have a schedule that has a task name column, a date column and a task type column. I am trying to build a formula (in another sheet) that will return the latest date based on when the task type is "APP" and the task name contains "GS" somewhere in the cell. Here is the formula I have come up with: MAX(COLLECT({Schedule…