Jun 15 2022 11:09 AM
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!
Jun 15 2022 12:11 PM
@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))
Jun 16 2022 08:54 AM
Jun 16 2022 10:41 AM
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.
Jun 16 2022 04:15 PM
Jun 17 2022 05:09 AM
Jun 17 2022 06:30 AM
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.
Jun 17 2022 06:45 AM
@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.
Jun 17 2022 08:21 AM
Jun 17 2022 08:21 AM
Jun 17 2022 09:14 AM
Jun 17 2022 09:20 AM
Jun 17 2022 09:57 AM
@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?
Jun 20 2022 09:00 AM
Jun 20 2022 09:14 AM
Jun 20 2022 09:52 AM
Jun 17 2022 06:30 AM
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.