SUMIFS for Not Equal (<>)
I have a SUMIFS formula that works:
=SUMIFS({GLData Range 1}, {GLData Range 6}, (@cell = "RES602835"))
I need to add a bit that would look at another column (let's call it GLData Range X) and pull in results that don't equal "turkey".
I've tried using <> as well as NOT but can't seem to get it to work. I wind up with a result of 0 which is not correct.
Thanks.
Best Answer
-
Does it work if you remove the quotes from around the 96600?
{Fund}, <>96600
Answers
-
Try:
SUMIFS({GLData Range 1}, {GLData Range 6}, @cell = "RES602835", {GLData Range X}, NOT(CONTAINS("Turkey", @cell)
-
I see but no. Incorrect result is still showing.
-
First, as a reminder - Best practice is to always name your ranges, so that way in the future you know what you are looking at if you have to trouble shoot or reverse engineer what you were doing. Or in case your formula got deleted and you need to decommission references at the sheet level, you can do so because you know what they are exactly.
Second, have you written it like this?
=SUMIFS({GLData Range 1}, {GLData Range 6}, (@cell = "RES602835"), {GLData Range X}, <>"Turkey")
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with!
-
Thanks Michelle. Named the ranges as suggested and this is the result:
=SUMIFS({Amount}, {SType}, (@cell = "RES602835"), {Fund}, <>"Turkey")
However, this results in "0" which I know is not the answer. I'm probably overlooking something simple but can't determine what.
-
Are you able to provide some screenshots for reference? Can you provide a screenshot of a row that should be getting countd but is not?
-
Thanks for the response and offer to assist. Attaching two (2) snapshots—one that should not be counted and one that should be counted. The trigger is the Fund Column ("Turkey"). I'm trying to exclude those with a Fund number of "96600".
Thanks again.
-
And what is the exact formula you are actually using?
-
=SUMIFS({Amount}, {SType}, (@cell = "RES602835"), {Fund}, <>"96600")
-
What does this do?
=SUMIFS({Amount}, {SType}, @cell = "RES602835", {Fund}, @cell <>"96600")
-
Thanks Paul. However, still getting a result of "0".
-
How exactly is the Fund column being populated?
-
The entire spreadsheet is updated/populated via Data Shuttle. The data behind the Data Shuttle upload is in an MS Excel file.
-
If you click into a cell in the Fund column as if you are about to edit it, is there a leading apostrophe?
-
No. The data in the Excel sheet is also a number, not text.
-
Have you applied any formatting to the Fund column?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!