SOLVED

Sumifs >=

Copper Contributor

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!

15 Replies

@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))
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

@Renattae_Schmidt 

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.

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.

@Sergei Baklan 

 

Please find attached an example of the work book I am using. 

 

Thank you

best response confirmed by Renattae_Schmidt (Copper Contributor)
Solution

@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.
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.
you set one 'filter' as "*Sobeys*" so it will also include "Sobeys Urban Fresh", so did you want to include that also?
Also in this formula you have >200 but it gets much bigger if you include >=200 as you noted in the original post.
I do want to include Sobeys Urban Fresh. I tried replicating the filters the formula should be doing to spot check my work, and I don't get the same number.

@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?

mtarler_0-1655485046989.png

 

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!
Yes if you filter for <200 then 195 is what you get. The formula is filtering for >200 and the original post mentioned >=200 ...
Oh my goodness, you are 1,000 right! Sorry for wasting your time, the formula works like a charm!

1 best response

Accepted Solutions
best response confirmed by Renattae_Schmidt (Copper Contributor)
Solution

@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.

View solution in original post