Forum Discussion
Sumifs >=
Renattae_Schmidt So that column C is text. The formula is =RIGHT(B2,3) which returns text. Even though you set the 'formatting' of that column to formatting style called NUMBER it doesn't convert that text to numbers. It is confusing, but it only sets the formatting of actual numbers in that column to a certain format. That said you can fix you issue by simple changing that formula to =--RIGHT(B2,3)
The "--" before the RIGHT performs a multiplication by -1 twice and Excel's Formula Calculator DOES try to convert the text into a value. It is shortcut to the alternative function, which you could also use: NUMBERFORMAT()
I also recommend formatting the table of data as a Table (HOME -> STYLES -> Format as Table) and then use the table structure references so you aren't forcing Excel to look at the whole column of empty values.
In the attached I updated it accordingly and gave examples of both.
Thank you for the response, unfortunately the formula you gave give me a #VALUE error. Also, column C is formatted as a number.
Any other suggestions?
Thanks,
Renattae
- As Sergei already mentioned it would be easier if you could attach the workbook or provide a link to it (no private, confidential, personal info). A simple test you could do is =C1+1 and fill down to see if any/all those cells have issues with the format.
That's better to discuss with sample file. Column C could be formatted as number, but contain texts (applying number format to "200" doesn't convert it to number 200). That could be non-printable characters if you copy/paste your data from Web. Whatever.
Renattae_Schmidt So that column C is text. The formula is =RIGHT(B2,3) which returns text. Even though you set the 'formatting' of that column to formatting style called NUMBER it doesn't convert that text to numbers. It is confusing, but it only sets the formatting of actual numbers in that column to a certain format. That said you can fix you issue by simple changing that formula to =--RIGHT(B2,3)
The "--" before the RIGHT performs a multiplication by -1 twice and Excel's Formula Calculator DOES try to convert the text into a value. It is shortcut to the alternative function, which you could also use: NUMBERFORMAT()
I also recommend formatting the table of data as a Table (HOME -> STYLES -> Format as Table) and then use the table structure references so you aren't forcing Excel to look at the whole column of empty values.
In the attached I updated it accordingly and gave examples of both.
