Sumif formula need help

[Jeremy Punnett]

I am trying to learn SUMIF. The examples given on the online examples are coming out as 0 on my spreadsheet? Both 1 and 2? Even though the function is working in another workbook. As I am trying to use it for something else I need to understand why it isn't working. I can't be the only one with this? I am running Office 365 Windows with all updates?

Thanks in advance.

[#2 · Accepted solution]

Hi Jeremy,

Please provide us with a sample of the date in your spreadsheet to be able to figure out what is the problem!

Anyway, the common cause of this issue is that the numbers in the Sum Range are in the text format as the below example:

Please find the attached file

Regards,

Haytham

[#3]

Hi 

Thanks for the response. I have got SUMIF to work on the spreadsheet for work I need but I cannot understand why this isn't working when I copied from MS site? It's bugging me and makes me think I have missed something bigger. The is clearly going to be a schoolboy error.

Thanks for your help.

KR/

Jeremy

[#4]

BTW I clocked what you were saying but I don't think that is the issue as I changed it to numeric format?

[#5]

This is the same cause that I talked about, please notice how numbers are aligned to the left which is the default alignment of texts in Excel!

To convert them to the numeric format, you need to highlight each column separately and keep it highlighted, then go to Data >> Text to Columns.

In the Text to Columns window, hit Finish.

And do the same thing for each column.

Hope that helps

[#6]

Or select the range with all cells with green triangle on top left and from alert icon drop-down menu select Convert to number