SUMIFS Multiple Criteria

I'm beating myself up.

Im trying to calculate the sum of tasks, that if they have the status "Submitted" and the submitted date is in March then it would calculate the value.

Currently it is saying its #unparseable

=SUMIFS([Submitted ($)]:[Submitted ($)], [Status]:[Status], "Submitted", [Date Submitted]:[Date Submitted], ">=03/01/17", [Date Submitted]:[Date Submitted], "<=03/31/17",)

Any help would be greatly appreciated

Find more posts tagged with

Formulas

Comments

Rob Hagan

Hi Nic,

I see a superfluous comma just before the closing parenthesis of the SUMIFS expression. Could that be the gremlin?

Cheers,

Rob.

John Creason

Nic,

I think there are a couple of challenges in the formula. Mulitple criteria in SUMIFS is a bit of a challenge until you get the hange of it... Beyond syntax of getting all the commas and parens in the right place, you need to think about how you're referencing dates.

Keep in mind that text you wrap in quotes gets treated as a string which means that less-than and greater-than operations won't always do what you expect. It's best to write formulas that are explicit about dates.

See if the following formula gives you what you need:

=SUMIFS([Submitted ($)]:[Submitted ($)], Status:Status, "Submitted", [Date Submitted]:[Date Submitted], AND(MONTH(@cell) = 3, YEAR(@cell) = 2017))

Note also that I've used the @cell operator to save adding a duplicate data and criteria pair for the Date Submitted column - the help article for for @cell can be found here:

https://help.smartsheet.com/articles/775363-using-formulas#at_cell

Kahart

Hello! I think I am having a similar issue and tried to mimic your formula above but no luck. Essentially I am trying to sum a dollar value in a column using 2 criteria from two other columns. These are also all columns from a separate sheet (all on the same sheet) so not sure if that is complicating things?

Here is my current formula-

=sumifs({Gains Worksheet Range 3}, {Gains Worksheet Range 1}, "Accounting", AND {Gains Worksheet Range 4}, "No"

Thanks!

J. Craig Williams

=SUMIFS({Gains Worksheet Range 3},

{Gains Worksheet Range 1}, "Accounting",

{Gains Worksheet Range 4}, "No")

a. {Gains Worksheet Range 3} - is the column to be summed.

b. you don't need the "AND"

c. Smartsheet will add the closing parenthesis. If it doesn't, there is something wrong.

I hope this helps.

Craig

Agnes

Hello,

I put the first step in smartsheet and try to use the sumifs formula that sum the sales volume by product code weekly from the other sheet that is quite simple in excel but is not working in smartsheet.

In one sheet I have:

Column A - Date

Column B - Product Code

Column C - Volume

In second sheet I try to sum the weekly volume per product:

Column A - the products codes

Column B - week 1 (01/01/18 - 08/01/18)

Column C - week 2 (08/01/18 - 15/01/18)

I will be grateful for you help.

Agnes

J. Craig Williams

Agnes,

I assume you are using either ISO 8601 or an EU calendar to have you weeks starting on Monday.

There are only 7 days in a week so

week 1: 1st to 7th

week 2: 8th to 14th.

I would add a row at the top for the week number (1-53) - though you only need 52 this year.

If your formula were in a single sheet, then it would look something like this:

=SUMIFS(Volume:Volume, Date:Date, WEEKNUMBER(@cell) = [ColumnB]$1, [Product Code]:[Product Code], [Product Code]23)

for row 23 and each of the week number columns

Replacing them with X-Sheet references will look something like this

=SUMIFS({Volume}, {Date}, WEEKNUMBER(@cell) = [ColumnB]$1, {Product Code}, [Product Code]23)

The formula can be copied to all week columns and their rows (below row 1) after you have set up the X-Sheet references for the first one.

Craig

Agnes

Hi,

Thank you your help.

In the meantime I tried to solve it and I have converted dates on weeks in separate column using WEEKNUMBER in first sheet and in second one used this formula

=SUMIFS({Volume}, {Code}, $Code1, {Week}, "1")

Seems it's working correctly.

Agnes

Stephanie Stoewe

I am trying to use this logic in a referenced sheet and I'm coming up with the wrong value. I'm asking the sheet to sum "Qty on PO" only if the Supplier matches the Parent, in this case Supplier 1, and the month and year correspond to the column I'm currently working in.

The formula is returning "1", but, I think it should be returning "1000".

=SUMIFS({Qty on PO}, {Supplier}, =PARENT(), {Month}, MONTH(@cell) = 1, {Year}, YEAR (@cell = 2019)

Any ideas?

Source Data 3.PNG

Summary Screen 3.PNG