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.
Also in this formula you have >200 but it gets much bigger if you include >=200 as you noted in the original post.
- Oh my goodness, you are 1,000 right! Sorry for wasting your time, the formula works like a charm!
- Yes if you filter for <200 then 195 is what you get. The formula is filtering for >200 and the original post mentioned >=200 ...
- Hello,
I am going a bit crazy trying to get the same number as you. My formula is also getting 1688, but if I filter manually I am getting 195.
Column C:C is filtered for anything less then 200, column O:O is filtered for anything with Sobeys, and column S:S is filtered for ON.
Any ideas why I am not getting the same number?
Thank you! Renattae_Schmidt I'm not sure what to tell you. What number do you think it should be? I got the same 1688 using the manual drop down filters as shown below. Notice in the bottom info bar the Sum: 1688
Any chance you were expecting 244? as in the COUNT?