Sumif woes

[SimonHughes]

Hi,

I am using Excel 2010

I am running a very simple formula to sum values in column C if the data in column A matched the value selected via a drop down box in cell B1. Trouble is it returns a 0. Could this be due to a formatting issue in the text?

Any help appreciated

=sumif(A:A,B1,C:C)

 

[Oaktree]

There are 3 likely cases for why you'd get your 0:

1) The conditional sum actually is 0
2) There are differences in text vs. numbers (e.g. A5 contains the text value "100" where B1 contains the number 100 or vice versa... in this case, consider changing your SUMIF formula to use B1&"" to convert from a number to text or B1+0 to convert from text to a number to make them consistent)
3) There may be leading or trailing spaces in column A's values. In this case, consider using =SUMIF(A:A,"*"&B1&"*",C:C) or similar, where the * is a wildcard

 

[Oaktree]

...As a note to my post above, Aladin kindly reminded me that (2) doesn't matter for SUMIFs. VLOOKUPs and other functions have an issue when text/numbers don't match, but SUMIF is not plagued by that mismatch.

 

[SimonHughes]

Many thanks Oaktree. It was a trailing space soa quick trim solved that one. So simple!