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.

SUMIF not working properly

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

I am trying to create some summary totals on an inventory sheet.  We stock several model Laserjet printers.  Each row has it's own sum.  Now I'm trying to create an overall sum of Laserjets by using the following formula:

 

=SUMIF([Inventory Item]1:[Inventory Item]86, "Laserjet", [In Stock]1:[In Stock]86)

 

If I interpret how SUMIF works in SmartSheet, this formula looks in the Inventory Item cells 1-86 for the word Laserjet and returns the sum of the associated cells in the In Stock cells 1-86.  However, it constantly returns the value of 0.  What am I doing wrong?  Is there some kind of wilcard I need to do with the work Laserjet to make this work?

Comments

  • ricki
    ricki ✭✭✭✭✭✭
    edited 03/21/17
    Options

    I think you need

     

    =SUMIF([Inventory Item]1:[Inventory Item]86, "Laserjet", [In Stock]:[In Stock]

     

    Leave off the numbers in the field you want the sum of

  • Brodiemac
    Options

    When I do that, it returns #INCORRECT ARGUMENT SET

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    Brodiemac,

     

    ricki's formula is malformed. The range size in the first and third argument need to be the same. 

     

    The names of your columns lead me to other possible errors.

    [in Stock] sounds like a check box column. 

    Is it?

    Or is the number of items in stock?

    (Both make sense, I just want to be clear. I will assume [In Stock] is a number of items.

     

    [Inventory Item] appears to be an item name.

    Are the names "Laserjet" only or do they contain other information like "Laserjet HP4100"

     

    If only Laserjet and a set of numbers then this should work:

     

    =SUMIF([Inventory Item]1:[Inventory Item]86, "Laserjet", [In Stock]1:[In Stock]86)

     

    for rows 1 to 86

    and this should work for all rows

    =SUMIF([Inventory Item]:[Inventory Item], "Laserjet", [In Stock]:[In Stock])

     

    But it appears you tried that.

    So, I'll assume that "Laserjet" is only part of the [Inventory Item] name.

    If so, try this

     

    =SUMIF([Inventory Item]1:[Inventory Item]86, FIND("Laserjet", @cell) > 0, [In Stock]1:[In Stock]86)

     

    Hope that helps.

     

    Craig

     

     

  • Brodiemac
    Options

    That did it Craig.  Your last formula hit the nail right on the head.  Thank you!

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    That pleases me.

     

    Craig

  • ryanjpugh
    Options

    Craig,

    I am having the same exact issue. I have a "Status" column with dropdown options of "Pending" and "Accepted". I have a second column with a dollar amount in each cell. 

    I want to sum the amount of all rows marked "Pending" and then do the same thing with all rows marked "Accepted". 

    I have tried: =SUMIF([Status]1:[Status]64, "Pending", [CAR Amount]1:[CAR Amount]64)

    as well as 

    =SUMIF([Status:]1:[Status:]64, FIND("Pending", @cell) > 0, [CAR Amount]1:[CAR Amount]64)

    with no luck. I keep getting $0 returned. Any help? Thanks.

    Ryan

     

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    ryan

    I assume you have limited the range to rows 1-64 on purpose and that is not the issue.

    The formulas look OK.

    The only thing I can think of is that somehow the data in the [CAR Amount] is Text and that the column is also formatted for percentage.

    The SUMIF formula is returning 0 which is formatted to use USD currency.

    Check if there is a formula used to get the value for [CAR Amount]. It should NOT have  a $ in the result or the result is being converted to Text.

    If that is not the case, you can share the sheet with me (craig.williams@ronin-global.com) and I can take look.

    Craig

  • ryanjpugh
    Options

    Thanks, Craig. I knew it would be something simple - the CAR Amount column was set as a Date column. Changing it to Text/Number immediately made the formula work.

    Ryan

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    Glad I could help.

    Craig

  • ArnaudLegrand
    Options

    Hello Craig

    i experiment the same issue

    sorry for my english but i m french

    i copy your formula below and try to adapt with "laserjet" type

     

    i want to make a sum if a name appear in the row i write it but doesn t work

    =SUMIF( [Syndic]1: [Syndic]1132]; FIND("Crouzet"; @cell) > 1; [Total HT]1:[Total HT]1132)

    in order to find "Crouzet" wich is a part of name ex "Crouzet Breil"

    I m new to Smartsheet et don t understand why

    Appriciate help of community

    after i will reuse the same formula to make some with different type of  name exemple "foncia", "taboni" but can appeat in row "cabinet Foncia" , or "cabinet Taboni"

    regards

    return error is #unparseable

    legrand@renovcanalisation.com

    Capture d’écran 2017-08-14 à 19.09.05.png

    Capture d’écran 2017-08-14 à 19.08.57.png

This discussion has been closed.