SUMIF Function help

**mikeu25** · 06-20-2012, 12:36 PM

HI, I am using Excel 2003
This is my sum if formula.
SUMIF('Sheet1'!$B:$B,'Sheet2'!$B5,'Sheet1!$W:$W)

My criteria, 'Sheet2'!$B5, is text and has values that are 22913, 12475, 0000022913, etc. All values are numeric, but are text. Note that some of the values have leading zeroes. So 22913 is different than 0000022913. The problem I am having is that say when 'Sheet2'!$B5 is 22913, the value 0000022913 in the range 'Sheet1'!$B:$B is seen as true and it will add in the corresponding value in the sum range 'Sheet1!$W:$W. I was able to solve this by surrounding the criteria with double quotes: SUMIF('Sheet1'!$B:$B,"'Sheet2'!$B5",'Sheet1!$W:$W)

However I need to copy down this formula, but when I do so the criteria remains $B5, not updating to $B6 in the next cell.

Thanks

**dilipandey** · 06-20-2012, 12:56 PM

Hi Mikeu25,

Welcome to the forum.

Just checked that Formula is working even without double quotes..

See attachment.
sumif issue Mikeu.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

**mikeu25** · 06-20-2012, 01:06 PM

Hi DILIPandey,

I think I was not clear on my original post.

If you change your formula to =SUMIF(Sheet1!$B:$B,"Sheet2!$B5",Sheet1!$W:$W) you get 0 for an answer and this is correct because 22913 <> 0000022913. What you sent in your spreadsheet is what I do NOT want to happen. Once I update to the above formula I get the result I want but I can't copy down the formula.

Thanks,

Mike

**dilipandey** · 06-20-2012, 01:26 PM

If you change your formula to =SUMIF(Sheet1!$B:$B,"Sheet2!$B5",Sheet1!$W:$W) you get 0 for an answer and this is correct because 22913 <> 0000022913.

I tried by having 0000022913 on both places but still got 0.

"Sheet2!$B5" - with quotes is the issue here... for my learning, can you show it to me where the formula is working with quotes. thanks

Regards,
DILIPandey

<click on below 'star' if this helps>

**mikeu25** · 06-20-2012, 03:15 PM

HI DILIPandey,

At this point I am not sure what to think. I was working with the excel example and just expanded on the use cases. I do not know what to make of the resutls. YOu can see from my attached excel that correct answers are in green and incorrect in red. Any comments would greatly be appreciated. I am thinking you dont use the double quotes and that there is a bug with the sumif function when you have text fields that just contain numeric data as I have explained above.

Thanks so much for your help,

Mike

sum_if_issue_expanded.xlsx

**dilipandey** · 06-21-2012, 01:43 PM

Hi mikeu25,

Few points:-

22914 0000022914 are not equal

Quotes should not be used in sumif formula as you have used:-
=SUMIF(Sheet1!$B:$B,"$A6",Sheet1!$W:$W)

Now, to make 22914 and 0000022914 as equal either you need to make:-

22914 as 0000022914 by using the formula as =TEXT(B14,"0000000000")
or,
0000022914 as 22914 by using the formula as =0000022914*1

Hope this helps..

Regards,
DILIPandey

<click on below 'star' if this helps>

**mikeu25** · 06-21-2012, 03:22 PM

Hi DILIPandey,

I think I am going to call it quits on this. I do not think I am communicating very well. I do not want 22914 and 0000022914 to be equal. They are different values. I think I will add a "N" to the end of each of my text strings and not use the double quotes in the SUMIF.

Thanks again,

Mike