SUMIF not adding correctly

NitaP

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?

Genevieve P.

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.

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.

hollyconradsmith

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)

NitaP

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.

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

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.

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

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.

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

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.

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.