LinkBack URL
About LinkBacks
Doing a sumifs formula and in criteria1 I have the following....
TEXT(10238,"#")
How do I change it to "not equal TEXT(10238,"#") ?
<>TEXT(10238,"#") does not work
Doing a sumifs formula and in criteria1 I have the following....
TEXT(10238,"#")
How do I change it to "not equal TEXT(10238,"#") ?
<>TEXT(10238,"#") does not work
Maybe = "<>" & Text(10238, "#")
Just tried it and it didn't work
Can you post your file?
Would like not to post it due to sensitive information.
My data is pulling in customer account numbers as text. I can change it but when the report refreshes, they change back to text. The current way I have it pulls in the correct number if the column equals 10238. If I can't get it to work I'll just double the formula and subtract one from the other to get the right number.
I changed it to this to come up with the right total but I'd like to make my formula short and simple if I can
=SUMIFS('36moHist'!$I$2:$I$35010,'36moHist'!$D$2:$D$35010,TODAY())-SUMIFS('36moHist'!$I$2:$I$35010,'36moHist'!$B$2:$B$35010,TEXT(10238,"#"),'36moHist'!$D$2:$D$35010,TODAY())
Maybe remove the sensitive data and replace with dummy data instead.
Maybe put the TEXT(10238,"#") in it's own cell and reference it?
What exactly is that supposed to be doing, anyway? Would "10238" not work just as well?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
"10238" does work also.
What I want is the total that does not equal 10238. I tried "<>10238" and it did not work until I went to the data and changed the 10238 to a number. Once I changed it to a number, the "<>10238" worked perfectly. The only problem was the "data" updates through out the day and it switches back to text.
How often does your data gets updated per day? If you highlight the column, do a Text To Columns, it will also convert Text to Numeric value.
I know you said you cannot upload your data, but can you mock-up something so we can see what you have?
I'm building a dashboard that the owner, myself, production manager, and the sales manager will open and review. I have it updating every 20 minutes pulling in real time sales, real time inputs and outputs and shipments. I did the "Text To Columns" for that column and it worked until the next update.
Sounds like your raw data is treating numeric value as text. Maybe change your criteria to <> 10238 + 0
Should be
=SUMIFS('36moHist'!$I$2:$I$35010,'36moHist'!$D$2:$D$35010,TODAY(),'36moHist'!$B$2:$B$35010,"<>"&TEXT(10238,"#"))
All I'm doing is saying, sum column "I" if column "B" does not equal 10238. And the only problem I'm having with it is column B is text.
To sum column "I" if column "B" equals 10238, TEXT(10238,"#") works perfectly. "10238" works also.
What does not work is
"<>10238"
<>TEXT(10238,"#")
"<>" & Text(10238, "#")
Last edited by rs1aj; 06-17-2015 at 03:12 PM.
Not sure if you missed my post, give this one a try see if it works.
"<> 10238 + 0"
=SUMIFS('36moHist'!$I$2:$I$35010,'36moHist'!$D$2:$D$35010,TODAY(),'36moHist'!$B$2:$B$35010,"<>"&TEXT(10238,"#"))
and
"<> 10238 + 0"
did not work
Both of these gave me 53. 17 of those equal 10238 so my total should be 36.
TEXT(10238,"#") give me a total of 17, which is correct but I want the total not equaling 10238
=SUMIFS('36moHist'!$I$2:$I$35014,'36moHist'!$D$2:$D$35014,WORKDAY(TODAY(),-1))-SUMIFS('36moHist'!$I$2:$I$35014,'36moHist'!$B$2:$B$35014,TEXT(10238,"#"),'36moHist'!$D$2:$D$35014,WORKDAY(TODAY(),-1))
does work so I may have to just stick with that.
Last edited by rs1aj; 06-17-2015 at 02:10 PM.
Well, if you really want to shorten the formula, then my final suggestion would be insert a helper column, convert the the text to numeric using Value() function, then do a SumIFS based on the helper column.
Sumifs (as you've seen) does tend to convert text numbers to real numbers.
Try sumproduct, it won't do that
=SUMPRODUCT(--('36moHist'!$D$2:$D$35014=WORKDAY(TODAY(),-1)),--('36moHist'!$B$2:$B$35014<>"10238"),'36moHist'!$I$2:$I$35014)
Thank you to all that helped.
Try this....
"<>*10238"
It worked on this simple example...
10238 10239 10240 10241 10242 10243 10244 10245 10246
C1=SUMIFS($B$1:$B$9,$A$1:$A$9,"<>10238")
C2=SUMIFS($B$1:$B$9,$A$1:$A$9,"<>*10238")
C9=SUM(B2:B9)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks