-2

I am using the Excel SUMIFS formulas to look for certain item numbers and add that value to the sum. The issue I am having is that many of our item numbers are very close to each other, i.e. 52000135890001 & 52000135890002.

The issue I am having is that the SUMIFS function seems to see these two items as the same value, and is suming them together. I want them added separately, as to us, they are essentially two different items.

I have tried changing the data type for the item numbers to every possible option but the formulas continues to group these together when performing the function.

Does anyone have any ideas how I can still use the SUMIFS formula but somehow tell it to see these two items as different, instead of adding them together under the same criteria?

I am at a loss and losing my mind so any advice would be beyond helpful!

Kyle

Kyle Ferguson
  • 1
  • 1
    What are the current criteria you are using in your SUMIF? We need more details – Jonathan Nov 19 '16 at 22:27
  • I just tried both numbers with `sumifs()` and it distinguished them. Also what version of Excel are you using? I am not sure but it may be possible with old versions to have more restriction. I see precision is limited to 15 and these are 14... – atclaus Nov 19 '16 at 22:38
  • Needs sample data and exact formulas used – Tim Williams Nov 19 '16 at 22:47
  • atclaus, you just identified my issue. The items I have having trouble with are longer than 15 digits, i.e. 10052000102397002, 10052000102397004. Do I have any options here without reducing the size of the SKU #? Since they are over 15 characters, this is why the SUMIFS is not picking up the last digit, which is what differentiated it from the other, – Kyle Ferguson Nov 20 '16 at 02:24
  • If numbers have more than 15 digits, not only SUMIFs formula, even cell doesnt differs them – bzimor Nov 20 '16 at 06:34
  • So, why not use RIGHT(...) and drop the left numbers 1005200, that way you will avoid the 15 character limit. Of course if the left most numbers become significant it won't work... – Solar Mike Nov 20 '16 at 09:13
  • Format all of your SKU cells as Text, or prepend all the values with ' – Tim Williams Nov 21 '16 at 02:12

1 Answers1

0

You format all your required cells (range cells, criteria and sum_range cells) to custom format as ##################', then apply the sumif it will work by using the right value.

Karpak
  • 1,927
  • 1
  • 15
  • 16