Forum Discussion
Solved
Sumifs >=
Hello all,
I am trying to sum all the units in column X assuming it says "Sobeys" in column O, and "ON" in column S and is >= 200.
For some reason the formula works if it is =200, but not if I put < or >.
For example, the below formula does not work and I just get 0.
=SUMIFS('Iri Comp Data'!$X:$X,'Iri Comp Data'!$O:$O,"*Sobeys*",'Iri Comp Data'!$S:$S,"ON",'Iri Comp Data'!$C:$C,">=200")
However the below formula counts all values of 200 perfectly.
=SUMIFS('Iri Comp Data'!$X:$X,'Iri Comp Data'!$O:$O,"*Sobeys*",'Iri Comp Data'!$S:$S,"ON",'Iri Comp Data'!$C:$C,">200")
I have also tried the formula and I still just get a 0.
=SUMIFS('Iri Comp Data'!$X:$X,'Iri Comp Data'!$O:$O,"*Sobeys*",'Iri Comp Data'!$S:$S,"ON",'Iri Comp Data'!$C:$C,"<200")
What am I doing wrong?
Thank you!
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.
Renattae_Schmidt
If you change in column C : right (B2,3) in value(right(b2,3)) for all cells, all formules you used will work correctly.- 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.
- Hi mtarler,
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 I suspect the issue with the values in column C being text values instead of number values. Try this:
=SUMPRODUCT('Iri Comp Data'!$X:$X * ISNUMBER(SEARCH("Sobeys",'Iri Comp Data'!$O:$O))*('Iri Comp Data'!$S:$S="ON")*(--'Iri Comp Data'!$C:$C >= 200))
