Trying to use SUMIF, but getting false returns due to leading zeros in criterion

M.OConnell

Using the formula below to poll a master sheet with all parts and components for many products we manufacture.

=SUMIF({All Product BOMs Range Part Number}, [Part Number]@row, {All Product BOMs Range Total Ext Demand})

Problem I'm having is, many of the OEM part numbers have leading zeros (such as 02034) which the grid returns as a '02034. That little ' is preventing SUMIF from pulling in that data.

I tried using COUNTIF, but I think because there are multiple instances (same parts may show up in multiple products) the COUNT is only grabbing the first one it finds, and not summing them.

Any thoughts?

Julio S.

Hi @M.OConnell,

Since Smartsheet will automatically convert Part numbers with starting 0s into text, the best way to accomplish what you intend would be to get rid of the initial 0s in the relevant part numbers. Since this might not be an ideal option, you may alternatively add a Helper Column with a Column Formula along the line of =VALUE(All Product BOMs Range Part Number) in your Master sheet that converts all part numbers into numeric values that can then be used in your SUMIF formula. Note that when taking this approach, the "Part Number" field in your formula sheet will also need to disregard any initial 0 before the part number:

Formula sheet:

Master Sheet:

 I hope this can be of help.

Cheers!

Julio

Julio S.

Hi @M.OConnell,

Since Smartsheet will automatically convert Part numbers with starting 0s into text, the best way to accomplish what you intend would be to get rid of the initial 0s in the relevant part numbers. Since this might not be an ideal option, you may alternatively add a Helper Column with a Column Formula along the line of =VALUE(All Product BOMs Range Part Number) in your Master sheet that converts all part numbers into numeric values that can then be used in your SUMIF formula. Note that when taking this approach, the "Part Number" field in your formula sheet will also need to disregard any initial 0 before the part number:

Formula sheet:

Master Sheet:

 I hope this can be of help.

Cheers!

Julio

M.OConnell

Thank you Julio. I will try that.