SUMIF not adding correctly

Options

I'm trying to add Units Used of a particular Raw Material with a certain Inventory Id. I've used the formula:

=SUMIF([Inventory ID]:[Inventory ID], [Inventory ID]@row, [Units Used]:[Units Used])

I am expecting a positive number, but the result it "0". If I copy the formula to all the rows in the Total Used column, all results are "0".

What am I missing?

Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Thanks for clarifying, @NitaP!

    I agree then with @hollyconradsmith. I would suggest adding in an entirely new Helper Column for the VALUE function as a totally separate formula:

    =VALUE([Inventory ID]@row)

    Then use this helper column in your formula so it finds a match:

    =SUMIF(Helper:Helper, Helper@row, [Units Used]:[Units Used])

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @NitaP

    Yes, I believe adding a letter in front of your IDs would resolve the issue, although it may also have to do with the column type you're using (ex. if it's in a Primary Column but you're comparing it to a text-number, etc).

    I would suggest first making sure your columns are consistent, and then if that doesn't work we could add the letter in front.

Answers

  • hollyconradsmith
    hollyconradsmith ✭✭✭✭
    Options

    Hi @NitaP,

    Smartsheet sometimes has trouble with values that start with 0's. Try using a helper column that removes the 0's from Inventory ID and see if that does the trick. (=value[inventory id]@row)

    Holly Conrad Smith

    Director of Technology & Innovation 💡 at Streamline

    CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony

  • NitaP
    NitaP ✭✭
    Options

    I tried this but still get zero:

    =SUMIF([Inventory ID]:[Inventory ID], =VALUE([Inventory ID]@row), [Units Used]:[Units Used])

    If I put the open parenthesis before =value, I get an unparseable error.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @NitaP

    Is it possible that your "Units Used" are not seen as Numerical? How are these numbers being input into your sheet, is it a manual entry or are you using a formula in this column as well?

    To test this, try simply using =SUM([Units Used]:[Units Used]) and see if you get 0

    Let me know what this returns!

  • NitaP
    NitaP ✭✭
    Options

    The values in the Units Used column in my SUMIF formula are numerical. I can sum them.

    I'm using a VLOOKUP formula to pull in the numbers for the Units Used column:

    =VLOOKUP([Inventory ID]@row, {Zit Zap 102621 Units Needed}, 2, false)

    The cell I'm referencing in that VLOOKUP formula is also a calculated field:

    =$[%]$5 * $[%]@row

    $[%]$5 is just a number, but $[%]@row is a percentage.

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Thanks for clarifying, @NitaP!

    I agree then with @hollyconradsmith. I would suggest adding in an entirely new Helper Column for the VALUE function as a totally separate formula:

    =VALUE([Inventory ID]@row)

    Then use this helper column in your formula so it finds a match:

    =SUMIF(Helper:Helper, Helper@row, [Units Used]:[Units Used])

  • NitaP
    NitaP ✭✭
    Options

    Thank you! Well, that worked, but it creates another concern. The Inventory ID column is not meant to be numerical. It's just an identifier. I use it a lot for VLOOKUP formulas. I haven't had any trouble until now. Should I change the ID to include a letter as a prefix so I won't run into this problem again? If so, what's the easiest way to do that?

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @NitaP

    Yes, I believe adding a letter in front of your IDs would resolve the issue, although it may also have to do with the column type you're using (ex. if it's in a Primary Column but you're comparing it to a text-number, etc).

    I would suggest first making sure your columns are consistent, and then if that doesn't work we could add the letter in front.

  • NitaP
    NitaP ✭✭
    Options

    I added a letter to my IDs this way:

    Added a temporary column with a letter to the left of my ID column and put the same prefix in every row.

    Added column to the right of my ID column and combined the Temp Prefix column with the ID column to create IDs with a letter prefix. =[Temp Prefix]@row + [Inventory ID@row.

    Copied all the calculated IDs with prefixes that were in the Temp ID column. and used PASTE SPECIAL - VALUES in the Inventory ID column.

    Then I deleted the two temporary columns I had created to the left and right of the Inventory ID column.

    I edited the column properties of my Inventory ID. It was already set to Auto-Number, but I added a prefix to the display format so that future ID numbers will also have the prefix.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @NitaP

    Thank you for outlining your process! That was a great idea, and should do the trick. Let me know if you're still seeing any formula issues after the update has been made.