Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

SUMIFS Multiple Criteria

Options
Nic
Nic
edited 12/09/19 in Archived 2017 Posts

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 

Tags:

Comments

  • Rob Hagan
    Rob Hagan ✭✭✭
    Options

    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
    John Creason Employee
    edited 03/09/17
    Options

    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
    Options

    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
    J. Craig Williams ✭✭✭✭✭✭
    Options

    =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
    Options

    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
    J. Craig Williams ✭✭✭✭✭✭
    Options

    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
    Options

    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

  • 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

This discussion has been closed.