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
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.
- Thank you for your response, but even when I put the above formula in column C, I get a number that is way higher then it should be. Indeed in the workbook you attached the numbers are higher then they should be.